辅导案例-ITNPBD3

欢迎使用51辅导,51作业君孵化低价透明的学长辅导平台,服务保持优质,平均费用压低50%以上! 51fudao.top
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.
51作业君

Email:51zuoyejun

@gmail.com

添加客服微信: abby12468