Assignment Task
Question 1 (Covers LO 2)
(a) Develop a semantically rich data model that captures the above scenario in the
form of an entity-relationship diagram (ERD). You should note (and number) all
assumptions you make about the data and the reasoning behind your design
choices. Also, include (and number) any appropriate constraints and a list of entity
types showing their attributes and identifiers.
(15 Marks)
(b) Once your ERD is complete, you should look for opportunities to use the entity
sub-typing concept and thus develop an EERD (Enhanced ERD).
(5 Marks)
Question 2 (Covers LO 3)
(a) Once you are satisfied that the EER diagram is a good representation of the
organisation’s data requirements, produce a logical design by mapping the EER
diagram to a set of relations, showing all primary and foreign keys clearly.
(10 Marks)
(b) Now produce a normalized relational model by checking each relation is in third
normal form (3NF). You should clearly annotate and explain this process.
(10 Marks)
(c) Under what circumstances would it be necessary to de-normalize this model?
(5 Marks)
Question 3 (Covers LO 1)
Name and briefly discuss three Database Management Systems (DBMS) that could
be used to implement a solution to your proposed database design. Decide on an
appropriate DBMS that you wish to use for this project. Provide an argument for
using this system rather than others that could be used and remember to relate your
reasons to the actual scenario. Be sure to fully reference any external sources used.
(10 Marks)
Question 4 (Covers LO 4)
(a) Take each of the normalized relations and implement them as SQL tables using
your chosen DBMS from the previous task with a series of CREATE TABLE
statements. You must include all primary and foreign keys as well as any other table
or column constraints you feel are appropriate such as NOT NULL, CHECK,
UNIQUE and DEFAULT. Provide screenshots of the working code. Ensure your user
name or some other distinguishing aspect is included in the screenshot to verify it is
your code.
(10 Marks)
(b) Using appropriate sample data and your own imagination based on this case
study, populate your finished tables with at least 10 rows of data in each table.
Provide screenshots of the working code. When done, display the full contents of
each populated table to screen and take screenshots. Ensure your user name or
some other distinguishing aspect is included in the screenshot to verify it is your
code.
(5 Marks)
(c) To demonstrate that your final database is useful, write a set of realistic sample
SQL queries based on the above scenario (use your imagination for details of each
query) but they should include the following techniques:
• • SELECT…FROM…WHERE…
• • Joins (using two, three or more tables)
• • Ordering output (ORDER BY)
• • Grouping output (GROUP BY)
• • Aggregate functions (MIN, MAX, AVG, COUNT, SUM)
You should aim to write a minimum of ten sample queries – ranging from basic
SELECT…FROM…WHERE queries to more advanced ones using the above
techniques. Be sure to fully evidence all SQL work by taking screenshots of the
inputted code and the outputted results. Ensure your user name or some other
distinguishing aspect is included in the screenshot to verify it is your code.
(20 Marks)
Question 5 (Covers LO 5)
Describe and explain how the following database application techniques may be
employed to enhance and extend the performance of your database:
• Indexes
• Triggers
• Stored Procedures
Ideally, you should attempt to implement (and evidence via screenshots) these
coding techniques but if that is not possible, then at least explain what they do and
what benefits they bring PLUS supplying the SQL code you would have executed.
