In general, a database design process contains information-level design and physical-level design.

A cumulative design is to support all user views being encountered during the database design process.

During the information-level design, we do not have to identify the primary key for each table.

To form a many-to-many relationship between two tables, we should create a new table whose primary key is the combination of the primary keys of the original two tables.

In general, a 2NF table is a 3NF table if we do not add any non-trivial MD into this table.

Given table Student ( StudentID , LastName , FirstName, GPA , BirthDate ), we may use LastName and FirstName together as the primary key.

Given table Student ( StudentID , LastName , FirstName, GPA , BirthDate ), we may specify LastName and FirstName together as a secondary key.

Given table Student ( StudentID , LastName , FirstName, GPA , BirthDate ), we may use LastName and FirstName together as an alternate key.

Given table Student ( StudentID , LastName , FirstName, GPA , BirthDate ), we may specify LastName and FirstName together as a foreign key.

Given table Student ( StudentID , LastName , FirstName, GPA , BirthDate ), we may use BirthDate as a secondary key.

There are three types of primary keys: natural, artificial, or surrogate.

The primary key SocSecNumber in table Employee ( SocSecNumber, LastName, FirstName, Salary ) is a surrogate or synthetic key.

The primary key StudentID in table Student ( StudentID, LastName, FirstName, GPA ) is an artificial key because this school assigns an unique 4-digit ID to each student of this school only.

Given the following two tables or entities with primary keys underlined: Client ( CID , CName , CAddress , CPhone ) Order ( OID , ODescription, Amount , Date , CID ) FK : CID -> Client we can say that Client is a weak entity because its existence depends on Order.

A survey form is used to collect information from the users.

Given the following three tables with primary keys underlined: Student ( SID , LastName , FirstName , GPA , BirthDate , Email ) Course ( CID , CName , CDescription , Units ) Take ( SID , CID , grade , year , semester ) we can determine that the relationship between Student and Course is one-to-many.

Given the following three tables with primary keys underlined: Student ( SID , LastName , FirstName , GPA , BirthDate , Email ) Course ( CID , CName , CDescription , Units ) Take ( SID , CID , grade , year , semester ) we can determine that all 3 tables are in 3NF, but not in 4NF.

Given the following four tables with primary keys underlined: Professor ( PID , PLastName , PFirstName , POffice , Phone , OfficeHours ) Student ( SID , LastName , FirstName , GPA , BirthDate , Email ) Course ( CID , CName , CDescription , Units ) PSC ( PID , SID , CID , grade , year , semester ) we can determine that PSC is in 4NF.

A null value is used when its value is unknown or inapplicable.

Database design is represented in Database Design Language (DBDL or DDL).

If a table contains a column that can be null for some rows, we may split this table by placing the null-able column in a separate table.

Database design is a two-part process: information-level design (not dependent on a particular DBMS) and physical-level design (appropriate for the particular DBMS to be used).

Given the following four tables with primary keys underlined: Professor ( PID , PLastName , PFirstName , POffice , Phone , OfficeHours ) Student ( SID , LastName , FirstName , GPA , BirthDate , Email ) Course ( CID , CName , CDescription , Units , StudyArea ) PSC ( PID , SID , CID , grade , year , semester ) we can determine that all four tables are in 4NF.

Survey form is useful for documenting the information that is gathered during the database design process.

Database designs can be represented visually by using Entity-Relationship (E-R) diagrams.

Physical-level design steps for each user view are to represent the user view as a collection of tables, to normalize these tables, to represent all keys (primary, alternate, secondary, and foreign), and to merge the results into the cumulative design.

Given the following three tables with primary keys underlined: Student ( SID , LastName , FirstName , GPA , BirthDate , Email ) Course ( CID , CName , CDescription , Units ) Take ( SID , CID , grade , year , semester ) we can determine that all 3 tables are in 4NF.

Given the following two tables or entities with primary keys underlined: Client ( CID , CName , CAddress , CPhone ) Order ( OID , ODescription, Amount , Date , CID ) FK : CID -> Client we can say that Order is a weak entity because its existence depends on Client.

One approach to represent an E-R diagram for a database is as follows: Entities are drawn as rectangles, and relationships are drawn as diamonds, with lines connecting the entities involved in relationships

When combining or merging 3NF tables, the result table might not be in 3NF.

Entity subtypes are complete categories if each object or record must be in one of the entity subtypes. Otherwise, they are incomplete categories.

Null values can be avoided by creating separate tables as entity subtypes.

Entity subtypes are called complete categories if there are some records that do not fall into any of the categories.

Given the following four tables with primary keys underlined: Professor ( PID , PLastName , PFirstName , POffice , Phone , OfficeHours ) Student ( SID , LastName , FirstName , GPA , BirthDate , Email ) Course ( CID , CName , CDescription , Units , StudyArea ) PSC ( PID , SID , CID , grade , year , semester ) we can determine that all four tables are in 3NF.

Given the following three tables with primary keys underlined: Student ( SID , LastName , FirstName , GPA , BirthDate , Email ) Course ( CID , CName , CDescription , Units ) Take ( SID , CID , grade , year , semester ) we can determine that the relationship between Student and Course is many-to-many.

Given the following three tables with primary keys underlined: Student ( SID , LastName , FirstName , GPA , BirthDate , Email ) Course ( CID , CName , CDescription , Units ) Take ( SID , CID , grade , year , semester ) we can determine that the Take table is in 3NF, but not in 4NF.

  

Given the following three tables with primary keys underlined: Student ( SID , LastName , FirstName , GPA , BirthDate , Email , Address ) Course ( CID , CName , CDescription , Units ) Take ( SID , CID , grade , year , semester ) we can determine that the relationship between Student and Course is one-to-one.