Reference no: EM132496323

BIT231 Database Systems Assignment - Melbourne Polytechnic, Australia

Assessment - Database Design Report

This assignment is cantered on ER modelling, a graphical tool used in database design and Normalization which is a text-based tool to remove unnecessary redundancy in a database. At the completion of these assessments' students will be able to:

1. Identify the different components of an ERD

2. Recognize some business rules from the relationships contained in an ERD

3. Understand and use the 3 different type of relationship classifications 1:1, 1:M, M:N and they're for the linking of tables in the RDM ((relational data model)

4. To develop relational models (RDM) where each table has a primary key (entity integrity) and some tables may have foreign keys (referential integrity)

Assessment Instructions -

1. Convert M: N relationships into 1:M and M:1 relationship.

2. Convert the conceptual ER model, given a problem scenario, into an RDM with appropriate primary keys (PKs) and foreign keys (FKs).

3. Only use Crow's Foot notation.

4. Draw dependency diagram to use the 1NF, 2NF and 3NF.

Projects Case Study Tasks -

Part A: Knowledge test

The knowledge test - Written computer-based assessment of Database Modelling and SQL concepts to work on the Assessment 1. The class test consists of short answers question about the basic concepts of Database modelling and Normalization.

Part B: Database Modelling and Implementation

Question 1 - ER Modelling

1. Business rules - Write Business rules to create ER Diagram. Write any assumptions.

2. Entities and Attributes - List Entities, Attributes - Include all attributes that you believe would be useful.

3. ER Diagram - Design an ER diagram for the above case study.

a. Create your ER Diagram with Visio/Lucidchart/draw.io or another piece of software. Hand-drawn diagrams will not be accepted.

b. Only use "crow's foot notation".

c. ER Diagram should include Entities, attributes, relationships, connectivity and cardinalities.

d. When completed, insert your ER Diagram into a Word document either by taking a screenshot of the diagram and pasting it in the word document, or by exporting the diagram as an image and the inserting it in the word document.

Question 2 - Data Dictionary

Create a data dictionary for the above ER Diagram using the following sample format (Hint: Recreate a table in word using the same column headings).

All data items must have suitable data types and all tables must have appropriate primary key and foreign key.

Question 3 - Normalization and Dependency Diagram

Briefly explain what is functional dependence and how does it relate to your solution?

Briefly explain why would we use normalisation and how is it accomplished?

Draw dependency diagram (using the following sample format) for your final ER Diagram, in the MS Word document, showing where necessary that you have progressed through to 3NF.

Question 4 - SQL Statements - DDL and DML

Based on the specification you have provided in the data dictionary:

i. CREATE TABLE - Write the SQL code to create all tables (in the ER Diagram and Data dictionary) to implement the relational data logical model (as ONE script).

ii. Constraints - In part i, define primary keys, foreign keys and NOT NULL constraints in the CREATE TABLE statement ONLY. [Note: a foreign key constraint requires the existence of the referenced table].

iii. Write SQL statements to Drop each table.

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

Note - Just need to do the Part B.

Attachment:- Database Systems Assignment File.rar