Qatar University Baby Day Care Center Relational Database Systems Project Task 1: Scenario: Consider the following scenario of a baby day care center. T

Qatar University Baby Day Care Center Relational Database Systems Project Task 1:

Scenario:

Consider the following scenario of a baby day care center. The owner wants to maintain a database for easy access to records related to the day care and other things. The requirements are as follows:

The parents seek admission to the day care center for their child. The owner wants to maintain a detail of the parents of the children seeking day care services. The new parents are given a new identification number. Other details regarding the parents will be the father’s name, mother’s name, the house address, father’s GSM, mother’s GSM to contact them, number of their children in day care. The details regarding the child includes: child identification number, gender, age, parent identification number as reference and the timings. When a child is admitted by the parent the owner wishes to record the following details: date of admission and the total fees. A parent may have one or more children admitted in the day care but a child will be associated to only one parent. There are many supervisors in the day care to care of the children. Each child is assigned a supervisor. A supervisor however may take care off one or more children. The day care also offers transport services. A child may avail the transport or may not avail. One transport has to be associated with at least one child but may carry many children.

a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, associative entities, attributes of each entity including primary key, relationship between the entities and cardinality constraints. State any assumptions necessary to support your design.

b) Analyze the above given scenario and discuss about the possible structure of any two relations by giving the details on the datatype and the size of each attribute. State any assumptions necessary to support your design.

c) Discuss the possible domain constraints to be implemented in the design given in (b) part to make the design better. State any assumptions necessary to support your design.

Task 2:

a) Normalize the below given Form to First Normal Form, Second Normal Form and Third Normal Form. Make assumptions for the identification of the primary key wherever necessary.

Note:

The Form has been uploaded inside file named “Form for Task 3 a” as word file.

b) Give a reflection of how normalization will help in easy maintenance of the database. Support your answer with reference to the above Question. (At least 150 words)

c) Discuss the implementation of integrity constraints in the above scenario. (maximum 400 words)

*** In-Text Citations and References using Harvard style.

*** File has been uploaded named “Form for Task 3 a” for task 3 a.

*** I have uploaded an example file for this assignment named “Assignment Database example”. ➢ Index:
Task2(a) Entity Relationship Diagram ………………………… 3
Task2(b) Description of the (ERD)………………………… 4-5
Task3(a) Normalization ………………………… 6
Task3(b) Benefits of Normalization ………………………… 7
References…………………… 8
1
Task 2
a) Entity relationship diagram (ER Diagram)
Phone no.
Email
Id
Name
Number
N
Pharmacy
N
Request
s
Manager
0..*
Purchase
department
Submits
Id
Placement date
Telephone
no.
Name
Fulfillment date
Id
Type
recieved date
Manufacturer id
Orders
1..*
Order amount
Manufacturer date
Consists
Drugs
Expiry date
1..*
Batch number
Date of supply
Unit cost
Supplied
AH
Date of supply
0..*
Wants
Supplier
Id
Track record
Name
Phone
no.
Location
Address
Email
Diagram 1: Entity Relationship Diagram
2
b) Description of the (ERD)
Pharmacy is an entity and comprises of attributes which are Id, Name and telephone number.
Pharmacy places requests to the Purchase Department whenever there is requirement of drugs.
Purchase department is an entity and comprises of attributes which are Number, Phone Number
and Manager. The purchase department submits many orders or might submit an order. Order is
an entity and comprises of attributes which are Id, placement date, fulfillment date, received date
and order amount. An order comprises of one or more drugs. A drug is supplied by one or more
suppliers. A supplier supplies many drugs or may not supply a drug. The drug is an entity and
comprises of attributes which are Id, name, type, manufacturer id, manufacture date, expiry date,
batch number, unit cost. A supplier is also an entity and comprises of attributes which are id,
name, address, phone number, email, location and track record. Whenever a supplier supplies a
drug, AH wants to record the shipment details such as date of supply.
Relationships between entities
Pharmacy requests to purchase department. A pharmacy can have one purchase department (one
to one relationship). A purchase department can submit many orders or might submit an order
(one to many relationship). An order comprises of one or more drugs (one to many relationship).
A drug is supplied by one or more supplier (one to many relationship).
3
As AH (Apollo Hospitals) wants to keep a track record of the shipment details like date of
supply, the proposed ER model includes the information about the track record and the shipment
details (date of supply of drugs) which will accommodate the payment and shipment details.
When AH wants to know the payment and shipment details, they can check this proposed ER
model and database which has a record of shipment details of drugs and enables them to
accommodate the payment and shipment details in the kept record.
Purchase administration has a record of the payment .Therefore, we can talk there is a
relationship between the payment and purchase department is (wages). The elements
of the
payment are (ID, Name, Payment date, Amount and Product), the primary key of payment is ID.
In addition, the shipment count on supplier because he is first official for shipping drugs to the
hospital. So, it has relationship between supplier and shipment is (ship), The attributes or
attributes are (ID, Shipment date, shipment volume, Market share, Growth), the primary key of
shipment is ID
Task 3
4
a) Normalization
Patient Bill description is normalizing into First Normal form, second normal form and third
normal form as well, which is as follows:
UNF
Bill Number
1NF
Bill Date
Bill Number (PK)
Bill Date
Registration Number
Registration Number
Prescription Number
Prescription Number
Patient Name
Patient Address
Patient Category
2NF
3NF
Bill Number (PK)
Bill Date
Bill Number (PK)
Bill Date
Registration Number
Prescription Number
Prescription Number
Doctor ID (,PK,FK)
Patient Name
Registration Number (,PK,FK)
Patient Address
Less 10%
Patient Category
Nett Payable Amount
Patient Name
Patient Address
Patient Category
Doctor ID
Doctor ID
Doctor ID
Doctor Name
Pres. Date
Drug ID
Drug Name
Quantity
Unit price
Total
Doctor Name
Less 10%
Doctor ID (PK)
Less 10%
Doctor Name
Nett Payable Amount
Nett Payable Amount
Bill Number (PK, FK)
Pres. Date
Registration Number (PK)
Bill Number (PK, FK)
Drug ID (PK, FK)
Drug ID (PK)
Less 10%
Nett Payable Amount
Doctor Name
Quantity
Drug Name
Total
Quantity
Pres. Date
Unit Price
Total
Patient Name
Patient Address
Patient Category
Bill Number (PK, FK)
Drug ID (PK, FK)
Pres. Date
Drug ID (PK)
Quantity
Drug Name
Total
Unit Price
Pres. Date
Drug ID (PK)
b) Benefits of Normalization
Drug Name
5
Unit Price
Normalization refers to make normal by means of producing something to follow to standard or
to present constancy with regards to content and style. In Relational database modeling terms,
constancy becomes procedure of eliminating repetition in data. Elimination of repetition or
duplication leans to reduce redundancy and reduction of redundancy involves getting free from
unrequired data available in specific table.
Data redundancy happens in database system which has area which is repeated in 2 or more than
2 tables. When drug data is repeated and attached with prescription date, than redundancy of data
is identified source of irregularity, as the entity “drug” may occur with different value for
particular attribute.
Data redundancy directs to data irregularities and shall be prevented when making a relational
database comprising of many entities. Normalization avoids redundancy as well as makes best
probable use of accommodation.
➢ How the redundancy has been minimized:
Normalization decrease redundancy and helps to organize data. Therefore that you can easily to
get the required data. In this example, I have of decrease redundancy of during division all the
data to three tables are 1NF, 2NF, 3NF. First stage, I wrote all the data in a patient prescription
invoice. Second stage, I have to Preparation 1NF, in these stage, I divided the to two tables:
repeating and non-repeating data, the linkage between the two tables is Bill number (PK) but in
repeating table Bill number is PK and FK. In 2NF, I have put a non-repeating table in the same
way no changing, but the repeating table I division to tow tables. I have put some data which
depend on the primary key in non-repeating (Bill Number is PK and FK), and I ingathering the
data in third table which depend on primary key (Drug ID). The last normal form is 3NF, I recall
the data in six small tables. firstly, I Determination all the primary keys which the data depend
on it. Then, I partition the non-repeating table to four tables first table containing primary keys
linking between other tables. The PK and FK in first table are: (Bill No, Registration NO, Doctor
ID). Second table I mention the doctor Id is a primary key. In third and fourth tables, the
primary key is (Registration number). But in last two tables, I put the same data without any
change.
6
References
➢ Reduce data redundancy. Relational DBS Design. [Online] Available from:
http://www.relationaldbdesign.com/relational-database-analysis/module3/reduce-dataredundancy.php [Accessed 4 December 2016]
➢ Bakery
entity
relationship
model.
CSC.
[Online]
Available
from:
http://www.csc.villanova.edu/~jkramer/BakeryER.html [Accessed 4 December 2016]
➢ Books Available in MIG
7
Al Khamis Shoe Store
Receipt
RECEIPT NO: 1233
DATE: 21/5/2019
TIME: 6:15
PM
BUYERID:CA1156
BUYER NAME: Aparna
SALESMAN ID: S23
SALESMAN NAME:BUDOOR
BUYER GSM: 86977080
PRODUCT
PRODUCT
NUMBER
DESCRIPTION
PRODUCT
CATEGORY
COLOR
S12
S67
S89
FORMAL
CASUAL
SPORTS
BLACK
BROWN
WHITE
JAHANARA
SHENAZ
TIPTOPS
SIZE
42
42
42
UNIT
QUANTITY PRICE
12.00
1 OMR
2 2.40 OMR
1 25 OMR
TOTAL
VAT
FINAL
AMOUNT:
SUB-TOTAL
10.00 OMR
2.20 OMR
25.00 OMR
37.200 OMR
2.00 OMR
39.20 OMR
NOTE: The attribute names chosen should be meaningful and should be different for each
student to avoid similarity in the submitted work. The students are advised to add the last
4 digits of their student id to the names of the entities to avoid similarity with other
students’ work.

Purchase answer to see full
attachment