Task $1-$ Database Design $(20$ marks$)$

Your first task is to design a database for the scenario detailed on the following pages. Your final database design

should include approximately $9$ entities.

State any assumptions $(2$ marks$)$ you have made regarding your database design at the beginning of the database

design document. Do not make any assumptions that significantly change the structure of the scenario, as this

may make Task $2$ of the assignment more difficult or inapplicable. Only make assumptions that influence your

database design in some way. If you are unsure about an assumption you wish to make, ask your tutor.

Once you feel you have identified the entities, attributes and relationships of the scenario in sufficient depth, you

are required to create logical and physical E$-$R diagrams $(6$ marks$)$ to depict your database. Adhere to the

distinctions between logical and physical E$-$R diagrams covered in Lecture $3.$ Use appropriate diagramming

software to create the E$-$R diagrams, and be sure to arrange the entities of your diagrams in a way that results in a

clear and easy to read layout. If you did well in Tasks $3$ and $4$ of Assignment $1,$ you should not need to make

many$/$any changes to your E$-$R diagrams.

Lastly, create a data dictionary $(10$ marks$),$ with an entry for each entity in your database. The entries should list

the name of the entity $($table$),$ a list of attributes $($columns$),$ important information about the domain of the

attributes $($e$.$g$.$ data type, null$/$not null, identity, default values...$),$ and details of any constraints applied to

attributes. List the entries in your data dictionary in an appropriate table creation order that can be used to create

the database. Include any additional information, if any, that may be needed to implement the database.

Remember, a data dictionary should contain all the information needed to implement your database design. Use

the data dictionary in Lecture $4$ and the data dictionary of the "company" database $($Module $5)$ as examples.

Some marks are also awarded for presentation and notation $(2$ marks$).$

Your complete database design should consist of a list of assumptions, logical and physical E$-$R diagrams and a data

dictionary. This should be submitted as a single PDF document, and be sure to open the PDF file before submitting

it to ensure that your diagrams appear as intended. Make sure that the first page of the document includes the

unit code, year and semester, and your name and student number.