BIT231 Database Systems Assessment 1 – Database Report

Assessment Weightage : 20%
Weightage distribution : Task 1 – 5% and Task 2 - 15%
Total Assessment Tasks : 2 Tasks
Total Marks for Task 2 : 130

This assignment is centered 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:

Identify the different components of an ERD Recognize some business rules from the relationships contained in an ERD Understand and use the 3 different type of relationship classifications 1:1, 1:M, M:N and their for the linking of tables in the RDM ((relational data model) 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

Convert M:N relationships into 1:M and M:1 relationships Convert the conceptual ER model , given a problem scenario, into a RDM with appropriate primary keys (PKs) and foreign keys (FKs) Only use Crow’s Foot notation Draw dependency diagram to use the 1NF, 2NF and 3NF

Submission Instructions

Must be submitted to Moodle ALL components of the assignment must be done in MS Word, including the ER diagram (If you are using Visio or some other tool export the image/snip the image and insert it into MS Word).

Projects Case Study

A construction company requires a database to record details about building projects. Each project has its own project number, name and employees assigned to it. Each employee has an employee number, name and job classification, such as engineer or computer technician.

The company charges its clients by billing the hours spent on each contract. The hourly billing rate is dependent upon the employee’s position. For example, one hour of a computer technician’s time is billed at a different rate than one hour of an engineer’s time.

The first step is to examine the data provided in the report below, which contains the relevant information

This represents the data as unnormalised and redundancy exists throughout the table. The operations (Business Rules) can be summarised as follows:

The company manages many projects. Each project requires the services of many employees. An employee may be assigned to several different projects. Some employees are not assigned and perform duties not specifically related to a project. Some employees are part of a labour pool, to be shared by all project team. For example, the company’s executive secretary would not be assigned to any one particular project. Each employee has a single primary job classification. This job classification determines the hourly billing rate. Many employees can have the same job classification. For example, the company employs more than one electrical engineer.

Tasks List

There are multiple tasks in the assessment

Task 1: Knowledge test (5%)

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 modeling and Normalization.

Task 2 : Database Modelling and Implementation (130 marks):

Question 1 – ER Modelling (55 marks):

Business rules - Write Business rules to create ER Diagram. Write any assumptions (10 marks) Entities and Attributes - List Entities, Attributes - Include all attributes that you believe would be useful (10 marks) ER Diagram - Design an ER diagram for the above case study. (35 marks) Create your ER Diagram with Visio/Lucidchart/draw.io or another piece of software. Hand-drawn diagrams will not be accepted Only use “crow’s foot notation” ER Diagram should include Entities, attributes, relationships, connectivity and cardinalities. 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 (20 marks):

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 (15 marks) :

Briefly explain what is functional dependence and how does it relate to your solution? (2 marks) Briefly explain why would we use normalisation and how is it accomplished? ( 3 marks) 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 (40 marks) :

Based on the specification you have provided in the data dictionary :
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). [10 marks deducted for code that does not work!] (15 marks) Write SQL statements to delete each table. Write INSERT INTO statements to populate each table. (at least 3 rows per table) (10 marks)
Please Note,
For part i and ii, only need to write CREATE TABLE statement ONCE for each table.
For each part, provide evidence that you have executed the statement in SAS studio. No evidence No marks.

The set textbooks for reference are : Rob, P, Coronel, C & Morris, S 2016, Database systems: design, implementation and management, 12th edn, Course Technology, ISBN-13: 9781305627482. Lafler, Kirk Paul 2013, PROC SQL: Beyond the Basics Using SAS®, 2nd edition, Cary, NC: SAS Institute Inc.

Plagiarism

All used sources must be properly acknowledged with references and citations, if you did not create it. Quotations and paraphrasing are allowed but the sources must be acknowledged. Failure to do so is regarded as plagiarism and the minimum penalty for plagiarism is failure for the assignment. The act of given your assignment to another student is classified as a plagiarism offence. Copying large chucks and supplying a reference will result in zero marks as you have not contributed to the report. Copying from Youtube or other videos is also plagiarism (including transcripts). Citation in a video can be included as credits at the end.

Late Submission

Late submission of assignments will be penalised as follows : For assignments 1 to 10 days late, a penalty of 5% (of total available marks) per day. For assignments more than 10 days late, a penalty of 100% will apply. Which means, the assignment will not be marked and attract a fail grade.
Your submission must be compatible with the software (PDF/Word/Video/Zip) in Melbourne Polytechnic, Computer Laboratories/Classrooms.

Extensions:

Under normal circumstances, extensions will not be granted. In case of extenuating circumstances—such as illness—a Special Consideration form, accompanied by supporting documentation, must be received before 3 working days from the due date. If granted, an extension will be only granted only by the time period stated on the documentation; that is, if the illness medical certificate was for one day, an extension will be granted for one day only. Accordingly, the student must submit within that time limit.

Penalties may apply for late submission without an approved extension.

Penalties:

Academic misconduct such as cheating and plagiarism incur penalties ranging from a zero result to program exclusion.
To Continue Click Here > BIT231 Database Systems Assessment 1 – Database Report.pdf