Designing and building database application

Reference no: EM132632783

ITECH 2004 Data Modelling - Federation University

Assignment 1: 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).
Learning Outcome 3: Interpret entity-relationship diagrams to implement a relational database.
Learning Outcome 4: Demonstrate skills in designing and building a database application using a commercially available database management system development tool.
Learning Outcome 5: Use a query language for data manipulation.
Learning Outcome 6: Design and implement a relational database using a database management system.
Learning Outcome 7: Utilise a query language tools and techniques to obtain data and information from a database.

Background
Toys 4 Uis acompanythat specialises intoy hirefor parties, kindergartens and play groups. They also offer equipment for infants and babies forhire. Toys 4 Ucan own several copies (TOYCOPY) of each toy(TOY). For example, a store may have 10 copies of the toyRocking 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 copiesbeing hired to a member (MEMBERSHIP). A copycan 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.

Documentation requirements
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

Reference no: EM132632783

Questions Cloud

Police agency-police administrator and media : Describe the relationship between the police agency/police administrator and the media. In what ways are there conflicts? In what ways are there partnerships?

Constitutional law : Does pluralism have any negative aspects? Why have some fought so hard against the concept in the United States?

United states function without federal constitution : Could a country such as the United States function without a federal constitution? Would it be possible for each state to merely abide by its own constitution?

Discuss what protective equipment : Discuss what protective equipment/gear and under what circumstances, if any, officers should be allowed to possess and or use and police protective equipment

Designing and building database application : Demonstrate skills in designing and building a database application using a commercially available database management system development tool

Strategies for negative and positive risks : Risk register with list of risks and potential responses; use of quantitative risk modeling through tornado diagram, decision tree diagram, or cost risk

Explain decision whether to drop or retain the wallpaper : Explain decision whether to drop or retain the wallpaper. Contemporary Trends sells paint and paint supplies carpet and wallpaper at a single store

What extent do personality traits types impact the manner : What extent do personality traits and personality types impact the manner in which leaders deal with the current COVID-a9 pandemic

Find what is thomas direct labour price variance for june : Find What is Thomas' direct labour price variance for June? Thomas produced 5,000 stopwatches and used 5,150 hours of direct labour at a total cost of $102,500.

User Account

All Pages