Module 06a - Creating Tables

Question 1

Database design

Answer 1

A database design is a set of database specifications that can actually be implemented in a specific DBMS product.

DB design = Logical design + some physical design

Question 2

Three design stages

Answer 2

Conceptual design (conceptual schema)

Logical design (logical schema)

Physical design (physical schema)

Question 3

Steps for Transforming a
Data Model into a Database Design I

Answer 3

Question 4

Steps for Transforming a
Data Model into a Database Design II

Answer 4

Question 5

Steps for Transforming a
Data Model into a Database Design III

Answer 5

Question 6

How to select the primary key

Answer 6

• The ideal primary key is short, numeric, and fixed.
• Surrogate keys meet the ideal, but have no meaning to users.

Some database designers take the position that, for consistency, if one table has a surrogate key, all of the tables in the database should have a surrogate key. Others think that such a policy is too rigid; after all, there are good data keys, such as ProductSKU (which would use SKU codes discussed in Chapter 2). If such a key exists, it should be used instead of a surrogate key. Your organization may have standards on this issue that you should follow.

Be aware that DBMS products vary in their support for surrogate keys.

Microsoft Access 2013, Microsoft SQL Server 2014, and MySQL 5.6 provide them.

Microsoft SQL Server 2014 allows the designer to pick the starting value and increment of the key, and MySQL 5.6 allows the designer to pick the starting value.

Oracle’s Oracle Database, however, does not provide direct support for surrogate keys, but you can obtain the essence of them in a rather backhanded way, as discussed in Chapter 10B.

In this book, we use surrogate keys unless there is some strong reason not to. In addition to the advantages described here, the fact that they are fixed simplifies the enforcement of minimum cardinality, as you will learn in the last section of this chapter.

Question 7

Specify Candidate (Alternate) Keys

Answer 7

• The terms candidate key and alternate key are synonymous.
• Candidate keys are alternate identifiers of unique rows in a table.
• Will use AKn.m notation, where n is the number of the alternate key, and m is the column number in that alternate key.

Question 8

What NULL means

Answer 8

Null status indicates whether or not the value of the column can be NULL.

Question 9

Data types

Answer 9

• Generic data types:
• Char(n), Nchar(n)
• Varchar(n), Nvarchar(n)
• Date
• Time
• Integer
• Decimal(m,n)
• Numeric(m,n)
• Money(m,n)

Question 10

Creata Table Statement

Answer 10

CREATE TABLE Persons
(
P_Id INT NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT ‘Sandnes’ NULL
);

Question 11

Data constraints

Answer 11

Data constraints are limitations on data values:

• Domain constraint—column values must be in a given set of specific values.
• Range constraint—column values must be within a given range of values.
• Intrarelation constraint—column values are limited by comparison to values in other columns in the same table.
• Interrelation constraint—column values are limited by comparison to values in other columns in other tables (referential integrity constraints on foreign keys).

Question 12

DDL Constraints

Answer 12

Constraints can be defined within the CREATE TABLE statement, or they can be added to the table after it is created using the ALTER table statement.

• Five types of constraints:
• PRIMARY KEY may not have null values
• UNIQUE may have null values
• NULL/NOT NULL
• FOREIGN KEY
• CHECK

Question 13

Create table example

Answer 13