The MediRAY chain of pharmacies has decided to develop a database system to store information of their prescriptions, patients, doctors and related details
SIT103 – Database and Information
Retrieval
The MediRAY chain of pharmacies has decided to develop a database system to store information of their prescriptions, patients, doctors and related details . They have given the development to your company and your manager has handed over the task to you. Below are the requirements.
• Patients and doctors are persons identified by the personal identification number. The person has address, email and phone number.
• Patients have patient number (the hospital and the patient number used here ), and their names, addresses, and ages must be recorded.
• Doctors have doctors registration number. For each doctor, the name, specialty, and years of experience must be recorded.
• Each pharmaceutical company is identified by name, address, email and has a phone number.
• For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company, and the trade name identifies a drug uniquely from among the products of that company. If a pharmaceutical company is deleted, you need not keep track of its products any longer.
• Each pharmacy has a name, address, and phone number
• Every patient has a primary physician. Every doctor has at least one patient.
• Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.
• Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors.
• Each prescription has a date and a quantity associated with it. You can assume that, if a doctor prescribes the same drug for the same patient more than once, only the last such prescription needs to be stored.
• Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical company can contract with several pharmacies, and a pharmacy can contract with several pharmaceutical companies.
• For each contract, you have to store a start date, an end date, and the text of the contract.
• Pharmacies appoint a supervisor for each contract. There must always be a supervisor for each contract, but the contract supervisor can change over the lifetime of the contract.
Use the description provided above about MediRAY. You can make your own assumptions based on the requirements given. However, the assumptions should be clearly indicated / defined.
Answer following questions.
Provide the following:
1. Identify business rules. Business rules are important to define relevant constraints in the organisations. Some organisations have specific rules only practiced by the organisation. (e.g. A business rule for a car sales company may be “for each car a separate invoice should be issued.”). You can make any appropriate assumptions and mention them in your answer.
(5 marks)
2. Design the database using Extended Entity Relationship (EERD) Diagram. Clearly
indicate entities, attributes, relationships, cardinalities, primary keys and other special types of attributes and relationships (e.g. multivalued attributes/ ISA relationships, etc.).
Note: Use correct symbols. If the symbols are incorrect your diagram cannot be considered as correct. You could use either crow’s foot or Chen’s EERD rules.
However, do not mix both.
And you should draw the EERD diagram using a Computer Aided Tool. You may use Lucidchart, word MS Visio or a similar tool.
(40 marks)
3. Develop relational schemas. Relational schemas should be derived using the EERD. You should map cardinalities correctly from EERD to Relational Schema. You should clearly indicate the referential integrity constraints (primary and foreign key relationships) using arrows. Clearly indicate datatype for each attribute.
e.g.
Emp( eid: integer, ename: string(50), address: string(100), did: number)
Dept(did:number, dname : string (15))
(20 marks)
4. Perform Normalization (including 3rd Normal form). In each normal form mention what you would consider and clearly show how would you remove redundancies. You should clearly indicate how the relational schemas are changing at each stage, and how you would adjust the referential integrity of the schemas.
(15 marks)
5. Write 5 queries with where clause that you think would be used by the MediRAY. You are required to clearly indicate the functionality of each query. You will not be given marks if you have not explained the functionality of each query.
(20 marks)
Submission: Submit a copy of your solution – a single document (pdf format – 5 penalty marks will be deducted for other format of submissions), electronically on the course portal, on or before the due date.
You must submit your Report with
• A cover page (with student number, name and a signed statement that states that all the work is your own work.)- Omission of one of the items will mean that 5 penalty marks will be deducted from the final mark.
• Answers for above mentioned questions. The design should be very clearly documented, and answers should be written in the order of the questions. If not, 5 penalty marks will be deducted.
Please see the marking scheme on next page.
Student ID and Name ________________________________________________
Question Comments Marks
1. Identify business rules
(5 marks)
You need to list any business rules identified.
/5
2. Entity Relationship Diagram (40 marks)
Identifying Entities /5
Identifying Attributes for each entity /5
Identifying Super and Sub entities /5
Identification of relationships /5
Identifying cardinalities /5
For use of correct symbols and for the clarity /5
3. Relational Schema (30 marks)
Mapping cardinalities correctly /20
Identifying and marking Primary and foreign Keys /10
4. Normalisation – each normal form 5 marks /15
5. Queries
Query 1 /4
Query 2 /4
Query 3 /4
Query 4 /4
Query 5 /4
Total /100
Retrieval
The MediRAY chain of pharmacies has decided to develop a database system to store information of their prescriptions, patients, doctors and related details . They have given the development to your company and your manager has handed over the task to you. Below are the requirements.
• Patients and doctors are persons identified by the personal identification number. The person has address, email and phone number.
• Patients have patient number (the hospital and the patient number used here ), and their names, addresses, and ages must be recorded.
• Doctors have doctors registration number. For each doctor, the name, specialty, and years of experience must be recorded.
• Each pharmaceutical company is identified by name, address, email and has a phone number.
• For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company, and the trade name identifies a drug uniquely from among the products of that company. If a pharmaceutical company is deleted, you need not keep track of its products any longer.
• Each pharmacy has a name, address, and phone number
• Every patient has a primary physician. Every doctor has at least one patient.
• Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.
• Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors.
• Each prescription has a date and a quantity associated with it. You can assume that, if a doctor prescribes the same drug for the same patient more than once, only the last such prescription needs to be stored.
• Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical company can contract with several pharmacies, and a pharmacy can contract with several pharmaceutical companies.
• For each contract, you have to store a start date, an end date, and the text of the contract.
• Pharmacies appoint a supervisor for each contract. There must always be a supervisor for each contract, but the contract supervisor can change over the lifetime of the contract.
Use the description provided above about MediRAY. You can make your own assumptions based on the requirements given. However, the assumptions should be clearly indicated / defined.
Answer following questions.
Provide the following:
1. Identify business rules. Business rules are important to define relevant constraints in the organisations. Some organisations have specific rules only practiced by the organisation. (e.g. A business rule for a car sales company may be “for each car a separate invoice should be issued.”). You can make any appropriate assumptions and mention them in your answer.
(5 marks)
2. Design the database using Extended Entity Relationship (EERD) Diagram. Clearly
indicate entities, attributes, relationships, cardinalities, primary keys and other special types of attributes and relationships (e.g. multivalued attributes/ ISA relationships, etc.).
Note: Use correct symbols. If the symbols are incorrect your diagram cannot be considered as correct. You could use either crow’s foot or Chen’s EERD rules.
However, do not mix both.
And you should draw the EERD diagram using a Computer Aided Tool. You may use Lucidchart, word MS Visio or a similar tool.
(40 marks)
3. Develop relational schemas. Relational schemas should be derived using the EERD. You should map cardinalities correctly from EERD to Relational Schema. You should clearly indicate the referential integrity constraints (primary and foreign key relationships) using arrows. Clearly indicate datatype for each attribute.
e.g.
Emp( eid: integer, ename: string(50), address: string(100), did: number)
Dept(did:number, dname : string (15))
(20 marks)
4. Perform Normalization (including 3rd Normal form). In each normal form mention what you would consider and clearly show how would you remove redundancies. You should clearly indicate how the relational schemas are changing at each stage, and how you would adjust the referential integrity of the schemas.
(15 marks)
5. Write 5 queries with where clause that you think would be used by the MediRAY. You are required to clearly indicate the functionality of each query. You will not be given marks if you have not explained the functionality of each query.
(20 marks)
Submission: Submit a copy of your solution – a single document (pdf format – 5 penalty marks will be deducted for other format of submissions), electronically on the course portal, on or before the due date.
You must submit your Report with
• A cover page (with student number, name and a signed statement that states that all the work is your own work.)- Omission of one of the items will mean that 5 penalty marks will be deducted from the final mark.
• Answers for above mentioned questions. The design should be very clearly documented, and answers should be written in the order of the questions. If not, 5 penalty marks will be deducted.
Please see the marking scheme on next page.
Student ID and Name ________________________________________________
Question Comments Marks
1. Identify business rules
(5 marks)
You need to list any business rules identified.
/5
2. Entity Relationship Diagram (40 marks)
Identifying Entities /5
Identifying Attributes for each entity /5
Identifying Super and Sub entities /5
Identification of relationships /5
Identifying cardinalities /5
For use of correct symbols and for the clarity /5
3. Relational Schema (30 marks)
Mapping cardinalities correctly /20
Identifying and marking Primary and foreign Keys /10
4. Normalisation – each normal form 5 marks /15
5. Queries
Query 1 /4
Query 2 /4
Query 3 /4
Query 4 /4
Query 5 /4
Total /100
-
- Assignment status: Resolved by our Writing Team
.
CLICK HERE TO ORDER 100% ORIGINAL PAPERS AT PrimeWritersBay.com
Comments
Post a Comment