ISYS1057 - Database Concepts Assignment 1 Important: Assignment 1 is worth 20% of your final grade Due Date: Sunday, 1st of September 2019 at 11.59pm 1. This assignment is to be attempted individually. 2. We will use the Canvas for assignment submission. A submission link will be enabled on Canvas closer to the submission date. 3. This assignment is worth 100 marks in total and the deliverable will be a single pdf named as ‘s3111111.pdf’ where ‘s3111111’ should be replaced with your actual student number that contains responses to all 4 questions. Submission Procedure and Late Penalties The individual must submit ONLY 1 PDF via Canvas. You can use Microsoft Word or another word processing application to work on your assignment. The diagram required for question 1 can be inserted into this document. Finally, you can save the document as a pdf. Else, if that option is not available on your system there are free pdf converters online you can utilise. e.g.http://convertonlinefree.com/ Remember: Submit one PDF only. Late submissions of assignments will be penalised as follows: Time Submitted after Due Date Late Penalty 0 to 1 hour no late penalty 1 hour to 1 day 10% of the total marks 2 days late 20% of the total marks 3 days late 30% of the total marks 4 days late 40% of the total marks 5 days late 50% of the total marks more than 5 days 100% penalty (you will get 0 marks) 1. ER Model (30 marks) 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. You must use only the following notation: Yes No 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. Do not create any new attributes not already provided in this business description. 2. The Relational Model (25 marks) 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. Yes relationName(attribute1, attribute2*) Relations not expressed in the approved representation are not permitted and will incur a penalty for this question. 3. SQL (35 marks) 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: 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. Each question is worth 5 marks. 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 (10 marks) 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. Plagiarism Notice Plagiarism is a very serious offence. Any submissions determined to be a result of plagiarism will be given zero marks for that assignment. In the event that a hurdle requirement is not met, this will result in the failure of the course. You should familiarize yourself with the contents of the university website for Academic Integrity. (http://www.rmit.edu.au/academicintegrity) All work is to be done individually and plagiarism of any form will be dealt with according to the RMIT plagiarism policy. Special Consideration If unexpected circumstances affect your ability to complete the assignment you can apply for special consideration. If you seek a short extension, you can directly contact me at:
[email protected] For longer extensions, you must follow the instructions provided at https://www.rmit.edu.au/students/student-essentials/assessment-and-exams/assessment/special-consideration An application for an extension must be made within two working days after the due date for this assessment.
