Reference no: EM133574243

Data and Information Management

Assessment: Database Analysis and Design Project

In your assignment you will need to analyse the following scenario, create ER diagram, build database and populate it with sample data.

A blood bank is a place where blood is collected and stored to be used by hospitals for those who need them either due to health emergencies or blood shortages. Blood banks are scattered all over places. So, it is important to have an organized database to help in allowing donors easily locate the nearest blood banks and donate blood, and also to make hospitals easily access blood when they need them within the shortest possible time.

Your task is to design and implement a centralised blood bank database that stores the following information:
- A Donor with the following attributes: Donor ID, Name, Date of birth, Gender, Address.
- A Blood unit donated by a donor. The blood unit attributes are blood code (unique), blood amount, donate date, blood type. Search Internet for blood types when populate the database.
- A Blood bank: blood bank ID, address.
- A blood bank manager is assigned to each blood bank. A manager can only be assigned to one blood bank. The blood bank manager attributes are manager ID, name, phone number.
- Hospitals always use blood from one assigned blood bank, and a blood bank can provide blood to several hospitals. The hospital attributes are hospital ID, name, address, email.
- A donor can donate blood in several blood banks, and at least one donor donates blood to a blood bank.

Your assignment consists of 3 parts:

Part 1. Conceptual Mode

Please remember that the Enhanced Entity-Relationship diagram is only a part of your assignment. You need to describe the Conceptual Model, which must include the following elements:
• Entities and their attributes. For each entity you must also specify unique identifier attribute(s)
• Entity supertypes and subtypes. Describe inheritance types and the corresponding discriminators.

• Identify multi-valued, derived, and composite attributes (if any). In this case, they need to be shown in the ER diagram with the corresponding notations.
• Describe relationships between entities and their cardinalities. Remember that relationships can be one-to-one, one-to-many, many-to-many. They also can be optional or mandatory.
• Draw the Conceptual Model Enhanced Entity-Relationship diagram using app.diagrams.net tool, save it as .png or .jpg file and insert it in the assignment Word document

Part 2. Logical (Relational) Model

Describe the algorithm of conversion of the Conceptual Model into the Logical Model. Remember that your description should include the following elements:
• Entities in the Conceptual Model become relations (tables) in the Logical Model, and unique identifiers become primary keys.
• For one-to-one or one-to-many relationship between two entities you will need to create a foreign key in one of the relations (tables).
• If there is a many-to-many relationship between two entities, then you will need to create a new relation (table) with a composite primary key.
• For each field (column) in a relation, describe a data type (integer, float, character, text etc.).
• If an entity contains a multi-valued attribute, then you will need to create a new relation in the Logical Model corresponding to this attribute.
• Draw the Logical Model diagram using app.diagrams.net tool, save it as .png or .jpg file and insert it in the assignment Word document.

Part 3. Physical design.
Using the database schema developed in the Part 2, create a database in MS Access, MySQL or SQLite Browser:
• Write and run SQL queries to create tables.
• Populate the tables with sample data.
• Write SELECT SQL queries to test the database.

Your submission should consist of the following files: Your submission should consist of the following files:
• MS Word document containing answers to the Parts 1 and 2.
• If you use MySQL database, then you also need to
o copy the queries in the MS Word document.
o include in the MS Word file screenshots of the XAMPP MySQL environment with queries and results of running these queries.
o export in XAMPP the database file with queries of creating the database tables.
• If you use MS Access database, then you need to submit the database file with all SQL queries.