COMPUTER SCIENCE AND TECHNOLOGY
Unit Name :
Systems Development and Modern Database Practices
Student’s Full names:
Student ID
Unit Co-ordinator’s Name: Antony Brown
project Leader Name: Antony Brown
project Title:
Resit project 2 all parts – Written report: Scenario-based Database System including SQL implementation
Note: This grade is provisional until agreed by the Field Examination Board.
Work received after the date shown will not be marked and you will receive a fail grade for the unit. The University will not accept responsibility for non-receipt of work unless the work has been returned by Recorded Delivery and proof of postage can be produced.
Please Note: Work presented in an assessment must be the student’s own. Plagiarism is where a student copies work from another source, published or unpublished (including the work of a fellow student) and fails to acknowledge the influence of another’s work or to attribute quotes to the author. Plagiarism is an academic offence.
I confirm this is my own work Student’s Signature ………………………….………………..
Tutor’s Grade Tutor’s signature ……………………………………………….
2nd Marker’s signature
(where appropriate) …………………………………………
Note: This project must be submitted using the Turnitin electronic submission system. And a hard copy submitted along with a CD of the database system handed in to University submissions desk No other form of submission will be accepted.
CIS020-2: Systems Development and Modern Database practices
Referral Individual project 2 Brief
CIS020-2: Individual Referral project – 2 Brief
Task
Your task for this assessment is to write a report, which explains how the below data is normalised and create the Oracle SQL code to create the structure.
The following steps should be carried out and presented clearly in your report:
1) You should normalise the data below, stepping through 1st normal form, 2nd normal form and 3rd normal form, explaining in detail each respective normalisation phase and justify why you have taken the steps you have.
2) Once you have normalised the tables, draw a full Entity Relationship (ER) diagram of the final normalised tables, using the notation specified during lectures.
3) Write the Oracle SQL Code to create the tables and relationships identified in your normalisation (Note: There is no need to write the commands to insert the data)
Deliverables
– Written report submission (100%)
o Submit report using the TURNITIN in BREO and optionally a hard copy to the university projects submissions office, adjacent to the main reception desk at Park Square.
o Ensure an project top sheet is included at the start of both electronic and hard copies.
Notes:
1. The client has requested that you do not add any additional codes/id numbers. Only use the data they have provided.
2. You should be as efficient as possible, so only store data that you cannot calculate otherwise
3. Salespeople can work for more than one supplier
4. Each Salesperson has a unique phone number
5. Supplier ID, Serial Number, Store ID and Category Code are all unique
Supplier Form
Supplier ID: S1246 Owner Name: Jedra Alo
Supplier Name: Kitchen Inc. Owner Telephone No.: 02387 382931
Street Address: 234 Reqdeam Road Category Code: K
Town: Birmingham Category Name: Kitchens
PostCode: BR2 6TY
Products
Serial Number
Product Name
Base Price
Store ID
Store Name
Store Address
Store Price
Markup
K83273
4-hob Oven
£200
KTCVF2
Joe’s Kitchens
56 Loco St, London
£300
£100
K83273
4-hob Oven
£200
KTCX00
Dezignz
200 High St, Derby
£280
£80
K99999
Sink
£75
KTCVF2
Joe’s Kitchens
56 Loco St, London
£150
£75
K00034
Deluxe Sink
£120
KTCX99
Outlet Store
77 London Road, Leeds
£150
£30
1.
2. Salespeople
Telephone Number
Name
0384 283733
Jake Simmons
0772 638133
Elsa James
Marking Scheme – Individual project 1 all parts
A Range: A sufficiently complex and excellent Entity Relationship (ER) model and conceptual schema which very clearly reflects the textual table presented earlier. The Normalisation process is carried out in a very logical and clear manner, very clearly and logically showing 1NF, 2NF and 3NF in logical steps. A final Normalised ER model clearly shows the logically presented tables including accurate optionality and all cardinality for all entities and relationships. Excellent SQL code for creating tables is included.
B Range: A sufficiently complex and excellent Entity Relationship (ER) model and conceptual schema which very clearly reflects the table presented earlier. The Normalisation process is carried out in a logical and clear manner, clearly showing 1NF, 2NF and 3NF in logical steps. A final Normalised ER model clearly shows the logically presented tables including optionality and all cardinality for all entities and relationships. Good SQL code for creating tables
C Range: A reasonable Entity Relationship (ER) model and conceptual schema which reflects the table presented earlier. The Normalisation process is carried out in a logical manner, showing 1NF, 2NF and 3NF in logical steps. A final Normalised ER model shows the resulting tables including some optionality and cardinality for entities and relationships. SQL code for creating tables
D Range: An Entity Relationship (ER) model which to some extent reflects the table presented earlier. The Normalisation process is carried out, showing a progression through the logical steps of normalisation. Some optionality and cardinality for entities and relationships may also be included. SQL code for creating tables
E Range: The Entity Relationship (ER) model does not sufficiently reflect the table presented earlier. The Normalisation process is not carried out to an adequate level, and does not show a clear logical progression through the steps of normalisation. Optionality and cardinality for entities and relationships may also be included but are in the majority of cases inaccurate
