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

By Hassnain Jamil -

I- Short Answer Questions: Answer the questions briefly. 


  1. Explain briefly referential integrity
  2. 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
  3. Distinguish between the primary key, candidate key, and surrogate key
  4. 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

CONTACT DETAILS

For any other questions or other tasks please feel free to contact me
via email: mhassnainjamil@gmail.com
via WhatsApp: +92-324-7042178
via skype: hassnainjamil1

Comments

Post a Comment

Popular posts from this blog

[SOLVED] Tape for a Turing Machine using Doubly-linked List in Java with full source code

By Anonymous -
Tape for a Turing Machine (Doubly-linked List) Short exercise in which you will code part of an existing project. In this case, you will be working with a doubly-linked list. To make this topic more interesting, the list that you work on will be part of a Turing Machine. A Turing Machine is a kind of very simple, abstract computing device that was introduced in the 1930's by Alan Turing as a way of studying computation and its limitations. The classes that you need for the lab are in a package named turing. You will define a new class named Tape in this package. You can find the Java source in the code directory. You should copy this directory into an Eclipse project. There will be errors, since the existing classes depend on the Tape class, which you have not yet written. Don't forget the Javadoc comments. A Class for Turing Machine Tapes A Turing machine works on a "tape" that is used for both input and output. The tape is made up of little squares called cell...
Read more

[SOLVED] ICT285 Databases TMA 2017 Assignment 1 | Relational Algebra, Database Design & Implementation

By Anonymous -
Relational Algebra A database records information about athletes competing at the Olympics. An athlete competes for a particular country in one or more events. Events take place at a scheduled day and time in a particular venue. The result (rank) is recorded for all athletes in the final of the event. The medal (gold, silver or bronze) is also recorded for the medal winners in the event. Note that we are not considering team sports or heats in this example – only individuals competing in the finals. The schema for this database is as follows: (Note that primary keys are shown underlined, foreign keys in bold). ATHLETE ( AthleteNo , AthleteName, CountryName) COUNTRY ( CountryName , NumberOfCompetitors) EVENT (EventName, ScheduledStart, VenueName ) VENUE (VenueName, City, Capacity) FINAL ( AthleteNo , EventName , Rank, Medal) Provide relational algebra (NOT SQL) queries to find the following informaList the name and country of all athletes. List the event name and scheduled...
Read more