Reference no: EM132624025

BIT358 Advance Database - Melbourne Polytechnic

Assessment - Database Design Report

Assignment Instruction:

Task 1 - ER Diagram

Create an ER Diagram using Crow's foot notation to represent the problem domain. (Use any suitable
diagramming tool e.g. Visio/Word)

i. Only maximum cardinalities are required
ii. Each entity needs to show the entity name, primary key , foreign key and atleast 4 more non-key attributes
iii. All M:N relationships are to be resolved into M:1/1:M relationships
iv. Any assumptions made must be stated

Task 2 - Relational Data logical model
Convert the above ER Diagram into a Relational Data logical model.

i. All primary keys must be underlined
ii. All foreign keys must be stated with bold and underlined format
iii. The relational table descriptions are to be in the form:

Task 3 - Data Dictionary

Create a data dictionary for the above relational data model using the following sample format. All data items must have suitable data types.

Task 4 - SQL Code

Based on the specification you have provided in the data dictionary. Create ONE SCRIPT for i, ii and iii.

i. Write the Oracle SQL code (in ONE script) to implement the relational data logical model. [10 marks deducted for code that does not work!]

ii. Define primary keys, foreign keys, NOT NULL, CHECK and UNIQUE constraints in the CREATE TABLE statement ONLY. You must use appropriate names for your constraints. [Note: a foreign key constraint requires the existence of the referenced table].

iii. (Oracle) - Choose an appropriate column to use the DEFAULT clause. Explain what is meant by DEFAULT and why this column is suitable to take such values.
OR
(SAS) - What is view in database? Create a view to display the teachers who are teaching Chemistry subject.

iv. Use appropriate INSERT INTO statements to populate each table. (at least 3 rows per table)

v. Use DROP TABLE statements for all tables in your script so that the database can be deleted and then created again every time the script is run.

vi. Demonstrate your knowledge of the ALTER TABLE statement by using three different examples in your script and explain briefly reason of writing this alter table command.

vii. (Oracle)This section requires some additional research. Ideally, your script would include a SEQUENCE. Using appropriate resources investigate the SQL sequence feature and use it to insert a value of the surrogate key such as SessionID. Display your knowledge of the use of sequences by the use of NEXTVAL. (HINT: NextVal should be used in the INSERT INTO statement)
OR

viii. (SAS) This section requires some additional research. List all the employees' full name by using PROC SQL Select statement and Macro variable assignment in SAS. Hint: Use PROC SQL with into and quit.

Attachment:- Database Design Report.rar