Database Modeling: Tables

Question 1

What is database modeling?

Answer 1

Developing a formal description of your DB, the schema, tables, columns, primary keys, relationships, etc. Don’t need fancy tools to do this; applies to any RDBMS; stay patient, methodical, step-by-step.

Question 2

Name three important topics to consider when planning a database.

Answer 2

1. What is this DB for? Get fairly specific here. Think hard about this.
2. What do you already have? Gather all the physical assets; fix problems b4 putting them in the DB; create entities for what you already have: these will become the tables. Single word description of what you have.
3. Think about vague relationships between the entities: an entity relationships (ER) diagram.

Question 3

What are attributes and data types?

Answer 3

1. Attributes - the columns in a table
2. Data type for each column - get specific about the type of data: text, character, integer, binary (photo, video, audio, document), etc

Question 4

When modeling a DB, is it better to be vague and flexible? Explain.

Answer 4

No. Define the columns as exactly as possible so the DB can enforce the rules. You do not want flexibility when planning and modeling relational DBs.

Question 5

What do we do if we cannot find a natural primary key for a table?

Answer 5

You can have the DB create the key (surrogate) by adding an ID column. The DB can then be told to increment this value each time a row is added. A table should always have a primary key.

Question 6

What is a composite key and when is it used?

Answer 6

A composite key is a primary key that is created by combining two or more columns. It is used when there is no natural primary key and you don’t want to use a surrogate (system-generated) key. Composite keys are often used with joining tables when creating many-to-many relationships.