IT 244

Design the database following an ER model. (15 marks) The ER should contain all necessary information such as entities, attributes, primary keys, relationships, cardinalities etc. Any assumptions should also be stated. Normalize the tables (each table should be in 3NF at least) (15 marks) Use MySQL or any other database to create the normalized tables. (10 marks) o You can use AMPPS software stack. It contains MySQL. https://www.ampps.com/downloads Populate your tables with 5 rows at least. (10 marks) Execute the sample requested queries. (30 marks) Each student should submit one report for each group about his/her project via the Blackboard contains the following: o Project title. o Group members information (name, id, email) o ER Diagram. o All schemas before and after normalization. o All SQL statements of: Creating tables Inserting data in tables Queries. Screenshots from MySQL (or any other software you use) of all the tables after population and queries results.

Consider a database system for a Blood Bank Organization. The data requirements are summarized as follows: 1. Donors: Donors are tracked by a unique donor ID. Personal information includes first name, last name, phone number, Date of Birth, and sex. The combination of the Donors first and last name must be unique. Donors sex is not a mandatory field. * Donors must be at least 17 years of age. Donors can donate at any clinic any number of times. 2. Blood: Blood is tracked by a unique ID. BloodType is how the blood is categorized: (A+/-, B+/-, AB+/-, O+/-, N/A). N/A refers to blood that has not been processed yet. Status refers to the state of the blood: (pure, N/A, impure). N/A refers to blood that has not been processed yet. Donor_id is a FK which references the ID of the Donor. Blood specimens must have a donor Clinic_id is a FK which references the ID of the Clinic that stores the blood. Blood specimens must be stored at one clinic. DonateDate is the date the blood sample was received. 3. Clinic: Clinics are tracked by unique clinic ID. Each clinic has a name and location. The combination of the clinics name and location must be unique 4. Employees: Employees are tracked by unique Employee ID. Clinic_id is a FK which references the Clinic ID which they work at. Employees must work at a single Clinic. Personal information includes Fname, Lname, and sex. The combination of an employees first and last name must be unique