Exam 1 study guide

Text: Chapters 1, 2, 3 (only ‘select’ statements)

Notes:

week01notes01, week01notes02

week02notes01, week02notes02

week03notes01

Labs:

1, 2, 3

For lab 3, must be able to read and understand Create Table statements containing primary and foreign key constraints and write insert statements to put data into these tables.

Terms:

Tables:

Relation, Attribute, Tuple

Table, Column, Row

Keys:

Composite key

Primary key

Candidate key

Foreign key

Concepts:

Why use a database instead of spreadsheets?

What is a relation?  (week01notes02)

Difference between a table and a relation?

What is referential integrity?  Be able to give an example.

Null values.  When do they occur in a database?

Functional Dependencies

What are they?

Given a set of fields, be able to:

1. find the functional dependencies in this data and state necessary assumptions.

2. break a single table into multiple well-formed relations using the normalization algorithm (week02notes01)

Here is my solution to question 2.39 on p. 92 (references fig 1-27 on p. 47):

Apply the normalization process to the Veterinary Office List – Version Two, shown below, to develop a set of normalized realtions.  Show the results of each of the steps in the normalization process.

1. Assuming:

Same assumptions shown in week02notes01 for ex 2.38, plus:

· No single animal can have the same service performed multiple times in a single day.

2. Minimal Candidate key is now:

(PetName OwnerLastName OwnerFirstName Service Date)

3. Same Non-candidate-key FD’s as in week02notes01:

Breed -> type

(OwnerLastName, OwnerFirstName) -> OwnerEmail

(OwnerLastName, OwnerFirstName) -> OwnerPhone

Plus:

(PetName, OwnerLastName, OwnerFirstName) -> PetBreed

(PetName, OwnerLastName, OwnerFirstName) -> PetType

(PetName, OwnerLastName, OwnerFirstName) -> PetDOB

To make the table well-formed (Text p. 67 & following), create Breed and Owner tables as in week02notes01 and also:

Create a new table Pet:

(Petname, OwnerLastName, OwnerFirstName, PetBreed, PetType, PetDOB)

Make referential integrity constraints:

VetOffice.PetName and Pet.PetName

VetOffice.OwnerLastName and Owner.OwnerLastName

VetOffice.OwnerFirstName and Owner.OwnerFirstName

# Notice PetBreed has moved from the VetOffice table to the Pet table, but this field is still a foreign key to

# the Breed table we defined in week02notes01

We now have well-formed tables:

Breed(PetBreed, PetType)

Owner(OwnerLastName, OwnerFirstName -> OwnerPhone, OwnerEmail) and

Pet(Petname, OwnerLastName, OwnerFirstName, PetBreed, PetDOB)

VetOffice(Petname, OwnerLastName, OwnerFirstName, Service, Date, Charge)

Queries

Given a database, be able to write SQL to:

• Select data from a single table using comparison (including LIKE), logical operators, and enumerations (IN).
• Sort data and use group by operator.
• Perform aggregate functions and eliminate duplicates.
• Use subqueries to build enumeration sets for use in outer queries.

DDL

Mysql SQL for creating and inserting into tables:

Constraints

Primary keys

Foreign keys