Reference no: EM132620669 , Length: word count:3000

ITECH 2004 Data Modelling - Federation University

Assignment: ERD and SQL

Overview

The purpose of this task is to develop student's skills in designing an ER diagram (ERD) and implementing this in a relational database for a given case study.

Learning Outcome 1: Design a relational database for a provided scenario utilising tools and techniques including ER diagrams, relation models and normalisation.

Learning Outcome 2: Describe relational algebra and its relationship to Structured Query Language (SQL). S1. Interpret entity-relationship diagrams to implement a relational database.

Learning Outcome 3: Demonstrate skills in designing and building a database application using a commercially available database management system development tool.

Learning Outcome 4: Use a query language for data manipulation.

Learning Outcome 5: Design and implement a relational database using a database management system.

Learning Outcome 6: Utilise a query language tools and techniques to obtain data and information from a database.

Assessment Details

Background

Toys 4 U is a company that specialises in toy hire for parties, kindergartens and play groups. They also offer equipment for infants and babies for hire. Toys 4 U can own several copies (TOYCOPY) of each toy (TOY). For example, a store may have 10 copies of the toy Rocking Horse. In the database, Rocking Horse would be one TOY, and each copy would be a TOYCOPY. A hire transaction (HIRE) involves one or more copies being hired to a member (MEMBERSHIP). A copy can be hired many times over its lifetime; therefore, there is an M:N relationship between HIRE and TOYCOPY. DETAILHIRE is the bridge table to resolve this relationship.

Requirements

1. Design your ER Diagram with all entity names, attribute names, primary and foreign keys, relationships, cardinality and participation indicated. You may add entities or attributes as you see fit. You will need to normalise all of your entities, to resolve any many to many relationships.
• Your attribute names, primary and foreign keys should be indicated as per the conventions given in the lecture slides (i.e. entity/table names in capitals, attributes as proper nouns, primary key underlined and foreign keys in italics).
• All many to many relationships should be resolved, and you should include a discussion of normalisation including the normal form that each entity is in and why that is optimal. Also, a discussion of how normalisation was achieved for that entity.

2. For each entity, create some example data for that entity. Include 5 rows of data for each entity.

3. Write the SQL code to create the table structures from your developed ERD. The structures should contain the attributes specified in your ERD. Use data types that are appropriate for the data that will need to be stored in each attribute. Enforce primary key and foreign key constraints as indicated by your ERD.
4. Write the SQL code to insert your data from (2) into the SQL table structures.

1. Prepare a business style report which contains the following: a). ER diagram based on defined scenario
b). Example data
c). A copy of your SQL code
d). Statement of any resources used. These includes full disclosure of assistance from all sources including tutors and other students. Full APA referencing of any resources used.

Attachment:- Data Modelling.rar