Database Modelling: Tables

Question 1

What is a schema?

Answer 1

This is the formal description of our database

This includes; columns, tables, keys, values etc.

Very important to plan schema to ensure smooth sailing from the very start of the database being online

Question 2

Questions to ask when starting database schema?

Answer 2

1. What’s the point? Be specific and ensure you understand the INTENTION of the database! What problem does the database solve?
2. What do you already have? Is there any infrastructure that is to be replaced or worked on top of/with? Are there any people and resources that you need or already have to help with the development of database?

Question 3

First REAL question to ask when designing database?

Answer 3

1. What entities will we have? Which objects need to be represented with a table. E.g. customers, oders, products, authros, employess. Use the most basic and simple words at the beginning
   NOTE: When naming tables based on these entities EITHER use singular (preferred) or plural

Question 4

What are out main concerns with our database?

Answer 4

1. Which entities we will have
2. How we will represent these entities
3. How we will draw relationships between these entities and their representations.

NOTE: This is known as entity-relationship modelling

Question 5

What is our second question to ask?

Answer 5

What are our attributes that we will assign to each entity?

Attributes can start relatively simple at the start of planning BUT they should be specific.
Have to determine a naming convention for each of the attributes AND STAY CONSISTENT

Question 6

Once entities and attributes have been determined, what is the next step?

Answer 6

Determine the data types for each attributes. Data types should be specific as DBMS require these to be specified.

E.g. character, date, integer, binary (audio, video, images) etc.

Question 7

What are BLOBs?

Answer 7

These are Binary Large Objects

These are data types that include images, videos and audio

Question 8

What are CLOBs?

Answer 8

These are Character Large Objects

These are data types that contain large sums of strings/text/characters

Question 9

What is a NULL value?

Answer 9

A NULL value states that there is a complete absence of a value within a table. Different from just zero.

Question 10

Choosing a primary key?

Answer 10

Primmary keys need to be added to tables to allow us to search for rows. They should be unique values to prevent confusion

Primary keys can be natural (an inherently unique value that is already an attribute in the table) OR can be synthetic (randomly generated number / unique value which ONLY acts as a primary key)

Question 11

What is a composite key?

Answer 11

This is when we use two OR more values within a table to create a unique primary key.

Can be used when joining tables together with a junction/linking table to form many-to-many relationships