Database Project (Database Normalization, ERD, Access Implementation and Basic Questions)

By Hassnain Jamil - Thursday, 25 February 2016 -

I- Short Answer Questions: Answer the questions briefly.

Explain briefly referential integrity
Using a sentence each, based on your understanding of database design, list 2 advantages of using a database system in comparison with using traditional electronic file processing systems
Distinguish between the primary key, candidate key, and surrogate key
In the following relation, which tracks payments made out to payees, Payment (Payment-Num, Payment-Amount, Payee-ID, Payee-Name, Payee-Address), the functional dependencies are: Payment-Num - Payment-Amount, Payee-ID, Payee-Name, Payee-Address Payee-ID - Payee-Name, Payee-Address. Please explain why this relation is subject to insertion anomaly and deletion anomaly

II- Suppose you are the database analyst designing a database for a swim team league called Water Fun League (WFL):

First, WFL wants to keep track of the swim teams in the league and their swimmers. Each swim team has an official name and a captain. A team is made up of many swimmers. WFL tracks each swimmer’s name, address, phone number, and date of birth. A swimmer can be a member of only one team.

Second, WFL wants the database to keep track of the swim meets it holds. WFL has a numbering system for each swim meet. A swim meet also has an official name (such as “Father’s Day Swim Meet”), and the time and location are announced as soon as available. All teams in the league can compete at each meet.

A meet consists of a variety of events (e.g., Men’s 50m freestyle, Women’s 100m butterfly, so on and so forth). The league keeps the League Record for each event as well as the corresponding national amateurs’ record as a reference. A swimmer can compete in multiple events at a swim meet.

Please design an ER diagram for the WFL database. Save it as XXXWFL.erd. Make any assumption you may need. STATE THEM CLEARLY. • The ER diagram must include the necessary entity classes and all attributes specified in the description.• Identifiers for all entity classes must be shown clearly. • Relationships must be named, with cardinalities clearly shown.

III- The following is the ERD for Garden Glory, a partnership that provides gardening and yard maintenance services to individuals and organizations. Covert the data model into a relational database design for Garden Glory (using MS Access). Save the database as XXXGardenGlory.accdb. IV- A small hair salon keeps the customer appointment information using the following file:

Appointments (Stylist-ID, Stylist-Name, Date, Timeslot, Customer-ID, Customer-Name, Customer-Phone, Type-of-Service)
The following dependencies hold true:

Stylist-ID - Stylist-NameCustomer-ID -Customer-Name, Customer-PhoneStylist-ID, Date, Timeslot - Stylist-Name, Customer-ID, Customer-Name, Customer-Phone, Type-of-Service

You are required to normalize the above schema to 3NF in a step-wise manner. At each step, clearly list all the relations in the schema, and show the primary key and foreign keys of each relation. You also need to draw an arrow from FK to its “parent” PK as shown in the examples we discussed in class.

STEP 1: How can you make the Appointments relation conform to 1NF?
STEP 2: Is your answer to Step 1 in 2NF? Why or Why not? Please articulate the reason(s). If not, normalize the schema to 2NF.
STEP 3: Is your answer to Step 2 in 3NF? Why or Why not? Please articulate the reason(s). If not, normalize the schema to 3NF.
STEP 4: Is your answer to Step 3 in BCNF? Please articulate the reason(s). If not, normalize the schema to BCNF.

Buy now

News Ticker

.