Skip to main content

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

Image
By

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
Labels:

Comments

Post a Comment

Popular posts from this blog

The Zoo Management System - entity relationship diagram & MS Access Database

By
Zoo Management System - Project Details: You are the employee of a big, worldwide working Zoo Management Company. Your company is responsible for the Zoo management. Your boss thinks it would be a great idea to store all data for each Zoo in a brand new self-developed ZOO Management System. Up to now, the ZOO management company has maps of each ZOO available. Your boss knows that you took a course in introduction on an ERP system, so he asks you if you could help designing such a system. Each ZOO must have the same organizational structure, which should look like this: Each Zoo has a Zoo-Address. Each Zoo has many visitors (Visitor Ticket Process (VTP). Many Zoo-Attractions belong to a Zoo. Module 1: Entity Relationship Diagram Design a ER (entity-relationship) diagram for your ZOO Management System. Use the information provided below with the entities and its attributes. Put the entities in the correct relationship to each other (organizational structure). Module 2: DB Implem...
1 comment
Read more

Human Physiology by Stuart Ira Fox [PDF] (12th edition) free download

By
21 comments
Read more

Student Registration System Web Project using HTML, CSS & PHP

By
Student Registry System In this task, you’re to further develop the student registry you started on lab-task. The solution is based the on the use of HTML5, stylesheets, and PHP, and will use a database for storage of information, MySQL on (school website) is used for this. Information of students is saved in the table student, ID is primary key and is set In addition to this, you need a table named class where id a is the primary key and is set up with AUTOINCREMENT, other columns and data types in accordance with the figure. The property class in the student table needs to be a foreign key that refers to the ID column in the class table. In MySQL you need to use tables of the type INNODB with support for foreign keys, reference integrity shall remain. The task: Write a class with the name StudentRegister (means Student registry), the class should use PDO and only prepared statements/questions. The class shall implement the interface StudentInterface, and use the two classes, Student,...
Post a Comment
Read more