Reference no: EM132358555

Database Concepts Assignment -

1. ER Model

This task can be completed using lucidchart.

If you create an educational account with your student email address it is free and unlimited.

OR

If you prefer to use something else you are more familiar with that is also ok. e.g. Visio, MS Paint, GIMP or even Google Drive.

Did you know you can create drawings in your Google drive?

Once you have created your diagram just insert it into your final document.

Use of Crows feet, Chen, IE notation or any other form is not permitted and will incur a penalty for this question.

According to the given description, construct an Entity Relationship (ER) diagram for the database, and make assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any concepts in the description that cannot be expressed in the ER diagram.

This question requires an ER diagram as a preliminary database design for a home building company. The database they require handles design and production information as well suppliers data. The following description explains there operation;

• Customers wanting a house register with the company by providing their name, email address and mobile number and they are given their very own unique C-code in return.
• For each house that is built, a project is created and stored in the database. A project record includes an address, start date, a project number to identify each project and a duration. Although wealthy customers may have several projects running concurrently, each project is owned by just one customer.
• Each house is built to a pre-defined design. There are a number of designs to select from. They all have their own names like "Texas Towers" and "Hampshire Humpy" and "Geelong Gem", etc. Key features of each design are captured in the database, such as; number of bedrooms, number of car spaces, number of floors and floor area.
• The important employees of the company are the people who build the homes and they are all in the database. Their name, employee number, Tax File Number, address and contact number are all available in the database.
• To save money, some items are pre-fabricated before being taken to the project site. This is done by pre-fabrication teams. Each team has a unique name (eg Windows Team, Kitchen Team), location and capacity (this is the number of units per week they can construct). Teams supply an assortment of items to all projects.
• Some employees are allocated to pre-fabrication teams. They can only work with one team at a time and a team can have several employees as team members. If an employee is allocated to a team, the date they started on that team is recorded.
• Many materials are required by the company for its business. Each material has a Category and within that general category, they have a Type. Some examples are, Bolt-Small, Bolt-Medium, Nut-Medium, Steel Strut- Medium, Bench Top - Grey. Each item has a UnitCost and UnitWeight.
• Both teams and projects require many different materials.
• The company deals with many suppliers and they are stored in the database. Suppliers have an Australian Business Number (or ABN) to identify each supplier, a contact number, a contact name, a description of what they supply.
• Suppliers supply materials. Each material is supplied by one supplier and Suppliers may supply several different materials.

2. The Relational Model

The following ER diagram describes the data needed for a Car Refurbishing company. Car are given a new engine then sent to dealer acorss the country. Fleet buyers have contracts with dealers in the areas they operate. Convert the following ER diagram into a relational database schema.

For each relation in your relational database schema, you should:

• Underline a primary key for each relation - each relation MUST have a primary key.
• Denote any foreign keys with asterisks(*) in your relations
• Make sure you create a relation for each entity and relationship you see.

3. SQL

This question uses the Research database available in Oracle and as a build file for use in SQLite.

A simplified ER model for the Research database is as follows (attached):

The Relational model for the Academics database is as follows:

DEPARTMENT( deptnum , descrip, instname, deptname, state, postcode)

ACADEMIC( acnum , deptnum*, famname, givename, initials, title)

PAPER( panum , title)

AUTHOR( panum*, acnum* )

FIELD( fieldnum , id, title)

INTEREST( fieldnum*, acnum* , descrip)

Write one SQL query for each question below to extract information from the database. Do not supply the output of the query. Only the SQL query is required for each question. All SQL must be presented in text format, i.e. no screenshots.

3.1. Display the given and family names for all academics whose academic number is in the range between 100 and 200 inclusive.

3.2. Display all department numbers and names for departments based in the state of Queensland in alphabetical order. Note: 'Queensland' is recorded as 'QLD' and 'Qld' in the database.

3.3. Display every paper title that has the word 'software' in its title. Your query must be case insensitive, i.e. display papers that have the word 'software' in any mix of upper or lower case.

3.4. Display the given and family names for all academics that are interested in 'Software Engineering'.

3.5. How many academics contributed to the paper that has the title 'Detection of mutual inconsistency in distributed systems'?

3.6. List each Field Number and the number of academics interested in each field. Only consider fields listed in the Interest table for this question.

3.7. It is known that the average number of academics in a department is 15. Use the Academic table to find the department number of all departments that have more than the average number of academics.

Hardcoding of identifiers not given in the question is not permitted and will incur a penalty for the question. Only use the information provided in each question.

Do not use the SQL keyword 'ROWNUM', 'FETCH' or 'LIMIT' to get a result.

Make sure your query performs one test that checks for upper or lower case versions of any names. e.g. 'TRISTAN EDWARDS', 'Tristan Edwards', 'tristan edwards' or even 'TrIsTaN eDwArDs'.

4. Short answer

4.1. Name all three Anomalies.

4.2. Define what a is Deletion Anomaly is and how can we avoid it in our database design?

Please use an example to explain.

Responses should be limited to no more than 2 or 3 sentences but this is not a strict requirement.

Attachment:- Database Concepts Assignment File.rar