User Account

All Pages

Modelling and normalisation, Database Management System

Assignment Help:
Task
Part A.
Create an ERD from a business problem. (50 marks)
Read the following problem and complete the tasks listed below.
THE ABS Blood Bank
Australian Blood Services (ABS) is a new Non-Government Organisation (NGO) that has just been set up to collect blood and blood product donations from the public. ABS plans to have donation centres in each capital city and most major regional cities throughout Australia. You have been approached by ABS to design and create a database system to track these blood donations.
ABS needs to record the details of each donor. This needs to include the donors full name and address details, along with their contact details, gender, Medicare number and any other details that may be pertinent.
ABS also needs to know about the medical condition of each donor or prospective donor. This includes the details of any surgery, existing medical conditions and any medication that is being taken.
ABS also needs to know if the donor has been overseas in the last three months, which countries that they visited, how long they stayed in each county, whether they contracted any disease while in the country.
Based on the answers to these questions, the ABS staff will decide if it is safe to accept a blood donation from the prospective donor. This is a decision can be made only by an ABS staff member who is a qualified Registered Nurse.
Once the ABS staff member has decided to accept a blood donation from the donor, it is necessary to record the donor details, staff member taking the donation, ABS donation centre, and any other pertinent details in order to clearly identify each individual blood donation.
Tasks:
1. List all the business rules for the ABS ( Business rules are important because they define the conditions that the database must meet. For example, every Order must be associated with a valid Product.This prevents invalid Orders being entered into the database). (7 marks)
2. Create an ERD that describes the ABS staff and donation centres. This ERD should show:(7 marks)
a. All entities with primary key and any foreign key attributes,
b. The relationships between entities,
c. The cardinality and optionality of each relationship.
3. Create an ERD that describes a blood donor, her medical condition and any medications that she takes. This ERD should show: (7 marks)
a. All entities with primary key and any foreign key attributes,
b. The relationships between entities,
c. The cardinality and optionality of each relationship.
4. The donor also needs to state whether she has been overseas in the last 3 months and if so, to advise the details of that trip. Add this information to the Donor ERD created in Q3 above. This ERD should show:(9 marks)
a. All entities with primary key and any foreign key attributes,
b. The relationships between entities,
c. The cardinality and optionality of each relationship.
5. Each donor can make a blood donation. Add this information to the Donor ERD created in Q3 and Q4 above. Donations must be made at an ABS Donation Centre and be taken by ABS staff. Add the ABS Staff ERD created in Q1 to the new Donor ERD. This combined ERD should show:(20 marks)
a. All entities with all attributes identified including Primary key and any foreign key attributes,
b. The relationships between entities,
c. The cardinality and optionality of each relationship;
d. Any M:N relationships are be resolved to 1:M relationships.
Part B. Create an RDM(15 marks):
Tasks:Using the ABS ERD created in Part A:1. Identify all attributes required for each table shown in the ERD (10 marks); and2. Create an RDM for each table in the ERD (5 marks);

Part C. Normalise the RDM to BCNF(35 marks):Tasks:Using the RDM created in Part B:1. Create a set of Dependency Diagrams for the ABS database (15 marks);2. Normalise the ABS tables to BCNF( 15 marks); and3. Create a new RDM for the normalised tables (5 marks).Marking criteria
The marking criteria for this assignment is:
Question HD DI CR PS FL
List and explain all business rules for ABS Comprehensive explanation of all business rules Detailed explanation of all business rules Thorough explanation of most business rules Adequate explanation of most business rules Incomplete or irrelevant explanation of business rules
Create an ERD for ABS & show all entities, PK & KF attributes,
relationships, cardinality & optionality
Comprehensive and accurate ERD that contains all entities, PK and FK attributes, associative entities included if required, all relationships shown and identified with correct cardinality and optionality Detailed and accurate ERD that contains all entities, PK and FK attributes, associative entities included if required, all relationships shown and identified with correct cardinality Thorough & reasonably accurate ERD that contains all entities, PK and FK attributes, associative entities included if required, all relationships shown with correct cardinality Adequate ERD that contains most entities, PK and FK attributes, associative entities included if required, all relationships shown with cardinality Incomplete or irrelevant ERD that contains some entities, relationships, and cardinality
Create RDM from ERD & show all attributes for each entity Comprehensive RDM that shows all attributes for each entity and has all PKs identified Detailed RDM that shows most attributes for all entities and has all PKs identified Thorough RDM that shows most attributes for all entities and has all PKs identified Adequate RDM that shows the major attributes for all entities and has most PKs identified Incomplete or irrelevant RDM that does not identify sufficient attributes for most entities.
Draw relational schema & show functional dependencies Comprehensive schema that shows existing PK and all functional, partial and transitive dependencies Detailed schema that shows existing PK and most functional, partial and transitive dependencies Thorough schema that shows existing PK and many functional, partial and transitive dependencies Adequate RDM that shows existing PK and some functional, partial and transitive dependencies Incomplete or irrelevant schema that does not adequately identify functional, partial or transitive dependencies
State normal form of relation and explain why Comprehensive description and explanation of current normal form Detailed description and explanation of current normal form Good description and explanation of current normal form Description of current normal form with poor explanation Incomplete or irrelevant description and explanation of current normal form
Decompose relation into 3NF and show relational schema for each relation and referential integrity constraints All relations in 3NF and all partial and transitive dependencies removed, each relation shows correct referential integrity constraints All relations in 3NF and all partial and transitive dependencies removed, each relation shows referential integrity constraints All relations in 3NF and all partial and transitive dependencies removed, most relations show referential integrity constraints All relations in 3NF and all partial and transitive dependencies removed, some relations show referential integrity constraints Incomplete decomposition or relations not in 3NF, no relations show integrity constraints

Related Discussions:- Modelling and normalisation

What are the four main characteristics of the database, What are the four m...

What are the four main characteristics of the database approach? The four major characteristics of the database approach are: 1. Self-describing nature of a database system. 2.

Support by adding indexes, Support by Adding Indexes Multiple indexes c...

Support by Adding Indexes Multiple indexes can be used to access a data file by multiple access paths. In such a scheme only single copy of the data is consider, only the numbe

Many-to-many relationship, For each m:n relationship type R, we make a new ...

For each m:n relationship type R, we make a new table (say S) to represent R. We also contain the primary key attributes of both the participating entity types as a foreign key att

What is called block-level striping, What is called block-level striping? ...

What is called block-level striping? Block level striping stripes blocks across many disks. It treats the array of disks as a large disk, and provides blocks logical numbers

Three level architecture of dbms-logical architecture, Three Level Architec...

Three Level Architecture of DBMS or Logical DBMS Architecture The logical architecture defines how data in the database is perceived by users. It is not deals with how the da

The proliferation of object-oriented methods, The Proliferation of Object-O...

The Proliferation of Object-Oriented Methods The first few years of the 1990s saw blossoming of around fifty various object oriented methods. This proliferation is a sign of g

Difference between left outer join and right outer join, Difference between...

Difference between LEFT OUTER JOIN and RIGHT OUTER JOIN In left outer join tuples from the left-hand-side relation which do not match any tuple in the right-hand-side relation

Er schema, Consider entity type Employee with attributes empNumber (this is...

Consider entity type Employee with attributes empNumber (this is unique), empName, and the year when he/she was hired. An employee can either be a technical employee, a marketing e

Develop a java application, 1 - Serial RS232 comm port comunication 2 - ...

1 - Serial RS232 comm port comunication 2 - NanoHTTPD serving a few processed pages 3 - System tray application to indicate status and access configuration forms 4 - SQLit

Normalize table 3nf , Normalize the following tables upto and including the...

Normalize the following tables upto and including the 3NF. Submit a 1 page printout of only the final set of normalized tables in Data Architect. Just use Data Architect to do the

Write Your Message!

Captcha
Order Assignment

Featured Services

Order Now

Popular Subjects

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

Submit Assignment

Academics

Major Subjects

Majors

Get In Touch

whatsapp: +91-977-207-8620

Phone: +91-977-207-8620

Email: [email protected]

TERMS & POLICIES

HELP & SUPPORT

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd