首页 > > 详细

代写ITNPBD3代写留学生SQL课程设计

ITNPBD3 Repeat 
Assessment 
Summer 2020 
 
Preamble 
This single piece of assessment is to be carried out by students who require a repeat of either the 
exam or the assignment from the spring 2020 ITNPBD3 module. The mark gained by the student will 
be used to replace any failed or missed grade from either the first assignment, exam or both. 
This assignment can be completed without access to database software. 
 
Assignment 
Read the questions carefully and provide exactly what is asked for. Check your answers against the 
list of things required for each question. 
Consider the following business scenario: 
A medical centre has patients and doctors. Doctors are responsible for many patients and each 
patient can be seen by more than one doctor. An appointment pairs a patient with a doctor at a 
given time and date. Doctors write prescriptions. Each prescription allocates a named medicine to a 
single patient and is signed by a doctor. Prescriptions cannot exist without an associated patient and 
doctor. Each appointment generates one or more prescription but each prescription comes from 
only one appointment. 
Each patient has a name, a unique health service number, a date of birth and a phone number. Each 
doctor has a name, a staff ID, gender, current salary and a list of specialisms. Each prescription has a 
serial number. Each medicine has a unique name, a list of side effects and a manufacturer. 
 
1. Identify all the entities in this scenario. For each one, give the name of the entity, list some 
of its attributes, and say whether it is a weak entity or not [5 marks] 
 
2. List the relationships between entity pairs. For each relationship, specify the two entities 
involved, the name of the relationship, its cardinality and its optionality. [5 marks] 
 
3. Draw an ER diagram showing the entities and relationships you identified in questions 1 and 
2 above. Choose any suitable notation, but be sure to show all the details. Include a key 
describing the notation you used. [10 marks] 
 
4. Design a suitable database schema by decomposing your ER design. For each table, list the 
following: [3 marks each] 
a. Table name 
b. Field names and types 
c. Primary key field(s) – indicate whether the primary key is natural or artificial 
d. Any foreign key fields and the tables they reference 
e. Which normal form you chose for the table. Justify your choice both in terms of the 
definition of the normal form and in terms of usability and integrity considerations 
 
5. Write the SQL code to create one of your tables. It does not matter which you choose. [5 
marks] 
 
6. Write the SQL code required to perform the following queries [3 marks each] 
a. Find the name of the patient whose health service number is 473431 
b. List the names of all the doctors in the database 
c. Calculate the average salary of all doctors 
d. Calculate the average salary of doctors grouped by gender 
e. Select all the specialisms of the doctor with staff ID 34332 
Now consider a solution to the same scenario using a document database like MongoDB. 
7. Draw a document model design for the database. Make sure your model includes at least 
one example of a document reference and one example of an embedded document. Show 
the collections and the structure of the documents they contain. [10 marks] 
 
8. Show two example documents in JSON format, each one from a different collection in your 
design. [5 marks] 
 
9. For each collection you identified above, choose appropriate fields to index and justify your 
choice. Explain what indexing achieves and how it is implemented. Why would you not 
simply index all of the fields? [5 marks] 
 
10. For each collection in your design, choose a suitable shard key. Explain what sharding is 
designed to achieve. [5 marks] 
 
11. When the database is small, it will fit on a single machine but imagine it grows to cover 
every medical centre in the county. It will need to run over a cluster. Discuss the 
consequences for database design, including considerations of consistency and availability. 
[5 marks] 
 
12. Write MongoDB commands to answer the following questions, based on the design you 
have given [5 marks each] 
a. Find the name (and only the name) of the doctor whose staff ID is 38543 
b. List the specialities of the doctor whose staff ID is 38543 
c. List all the patients ever seen by the doctor whose staff ID is 38543 
 
Submission 
Write your answers in a document and upload it to Canvas on the course assignments page. See the 
course page for other details such as deadlines and plagiarism rules. 
联系我们 - QQ: 99515681 微信:codinghelp2
© 2014 www.7daixie.com
程序代写网!