Database Concepts

ISYS1055/1057 Database Concepts
Assignment 3
Assessment Type: Individual assignment. Submit online via Canvas→Assignments→Assignment 3. Marks are awarded for meeting requirements as closely as possible. Clarifications/updates may be made via announcements/relevant discussion forums.
Due date: Friday 16 October 2020, 23:59. Please check Canvas→Syllabus or via Canvas→Assignments→Assignment 3 for the most up to date information.
As this is a major assignment in which you demonstrate your understanding, a late penalty of 10% of full available marks per day or part day applies for up to 5 days late. After 5 days, 0 marks will be awarded.
Weighting: 30 marks
1. Overview
Database systems are a key technology for the storage, management, manipulation, and retrieval of structured data. They have an impact on the use of information technology in applications ranging from banking, to travel bookings, to online shopping. In this assignment you will apply the skills and concepts that you have learned about database systems in the course so far.
2. Assessment Criteria
This assessment will determine your ability to:
1. Follow coding, convention and behavioural requirements provided in this document and in the lessons.
2. Independently solve problems by using database concepts taught over the first several weeks of the course.
3. Understand the relational model.
4. Independently design a database using the ER model.
5. Write and understand SQL queries.
6. Meet deadlines.
Seek clarification from your instructor, when needed, via specific discussion forums.
This assignment is worth 30 points in total for four questions (the mark associated with each question is shown in the relevant section), which accounts for 30% of the overall assessment for the course. The revised assessment components and weights for the course are:
Assessment 1
Assessment 2
Assessment 3
Assessment 4
3. Learning Outcomes
This assessment is relevant to the following Course Learning Outcomes:
• CLO 3: Identify issues with and compare, justify relational database design using the functional dependency concepts.
• CLO 6: Design a database schema using conceptual modelling mechanisms such as entity-relationship diagrams.
It also supports the following Graduate Learning Outcomes:
• Enabling Knowledge: You will gain skills as you apply data modelling knowledge effectively in diverse contexts.
• Critical Analysis: Analyse and model requirements and constraints for the purpose of designing and implementing software artefacts and IT systems.
• Problem solving: Design and implement database solutions that accommodate specified requirements and constraints, based on analysis or modelling or requirements specification.
4. Submission format
Page 2 of 6
Submit your assignment via Canvas→Assignments→Assignment 3. Your submission must be a single .pdf file, with the filename being your student number (e.g., S1234567.pdf) that contains the answers for each question below.
● Clearly number each question and sub-question in line with the numbering in the assignment (e.g. Q1.1, Q1.2, etc.)
● It is your responsibility to correctly submit your files. Please verify that your submission is correctly submitted by downloading what you have submitted to see if your file includes the correct content.
● Never leave submission to the last minute — you may have difficulty uploading files.
● You can submit multiple times – a new submission will override any earlier submissions. However, if your final submission is after the due time, late penalties apply.
● If unexpected circumstances affect your ability to complete the assignment, you can apply for special consideration. Special Consideration that extends beyond the release of solutions (typically 1—2 weeks) will automatically result in an equivalent assessment in the form of an online test and interview on the same topics (time to be arranged by the course coordinator).
● More information on special consideration is available at
5. Academic integrity and plagiarism (standard warning)
Academic integrity is about honest presentation of your academic work. It means acknowledging the work of others while developing your own insights, knowledge and ideas. You should take extreme care that you have:
• Acknowledged words, data, diagrams, models, frameworks and/or ideas of others you have quoted (i.e. directly copied), summarised, paraphrased, discussed or mentioned in your assessment through the appropriate referencing methods,
• Provided a reference list of the publication details so your reader can locate the source if necessary. This includes material taken from Internet sites.
If you do not acknowledge the sources of your material, you may be accused of plagiarism because you have passed off the work and ideas of another person without appropriate referencing, as if they were your own.
RMIT University treats plagiarism as a very serious offence constituting misconduct. Plagiarism covers a variety of inappropriate behaviours, including:
• Failure to properly document a source
• Copyright material from the internet or databases
• Collusion between students
For further information on our policies and procedures, please refer to the University website.
6. Assessment declaration
When you submit work electronically, you agree to the assessment declaration.
7. Rubric/assessment criteria for marking
The detailed rubric and assessment criteria are available online via Canvas→Assignments→Assignment 3.
Page 3 of 6
8. Assignment Questions
Question 1. The Relational Model (6 points).
Consider building a database to manage data for branches, employees, customers and accounts for the ABC Bank.
The entity types and their associated attributes are identified as follows, where composite attributes and attributes that can uniquely identify entities are indicated:
• Branch: branch-No (unique), address (composite: street-number, city, postcode).
• Employee: employee-ID (unique), employee-name.
• Customer: customer-ID (unique), customer-name.
• Account: account-number (unique), balance.
Relationships among entity types are as follows:
• A customer can have several accounts and an account can be owned by more than one customer.
• An employee only works for one branch but a branch can have many employees.
• A customer can have more than one account and an account may have more than account holder (customer).
1.1. (2 points) Give all likely FDs. Do not include trivial or redundant FDs.
1.2. (2 points) Give {customer-ID}+ based on the FDs in Question 1.1.
1.3. (2 points) Give relations to keep data for the entity types and their relationships as described for the ABC bank. Specify the primary key (underline) and any foreign key (asfor each relation. Note that relations are written in the form as shown in the examples below:
Student(sno, name, address)
Course(cno, title)
Take(sno*, cno*, grade)
Page 4 of 6
Question 2. Normalisation (9 points).
Consider the following relation schema about project meetings:
PMG(projID, title, type, manager, jobID, start-date, end-date, contractor, contractNo)
Some notes on the semantics of attributes are as follows:
• Each project has a unique project ID (projID) and also has a title, type and manager. Each manager has a specialty project type.
• A project often contracts jobs to contractors with start-date and end-date. Contracts are identified by contract numbers (contractNo), but contract details are out of the scope of the database.
FDs based on business rules are given as follows:
• projID → title, type, manager
• manager → type
• jobID → projID, start-date, end-date, contractor
• projID, title, jobID → contractNo
• contractNo → jobID, contractor, start-date, end-date
• jobID → contractNo
Answer questions below:
2.1. (3 points) The given FDs have redundancies. Give the minimal basis for the given FDs.
2.2. (3 points) The PMG relation is not in BCNF or 3NF. Explain why. Your explanation must be based on the functional dependencies in Question 2.1.
2.3. (3 points) Decompose the PMG relation into relations in BCNF or 3NF. Your decomposition must keep all functional dependencies and must be lossless. For each resultant relation, discuss if it is in BCNF or 3NF and indicate the primary key (underline) and any foreign keys (*). Note that relations must be written in the form as shown in the examples below:
Student(sno, name, address)
Course(cno, title)
Take(sno*, cno*, grade)
Page 5 of 6
Question 3. The ER Model (9 points).
You are tasked to design the ER diagram for the database for the 3S clothing store. Data requirements are as given below.
• Information about 3S employees includes a unique employee ID, and his/her name and position.
• Each customer of 3S is identified by a customer ID, and has a name.
• Information about the suppliers of 3S include a unique name, and a contact phone number.
• Clothes stocked by 3S are described by a product code, size, colour, set price and stock quantity.
• Details of clothing brands need to be kept, including a unique brand name and any designer information.
• A piece of clothing must have at least one supplier and each supplier can supply many clothes. When a supplier supplies a piece of clothing, data includes a supply price, and the quantity and date of the supply. If a supplier provides several supplies for a piece of clothing, only data for the most recent supply is kept.
• Customers order clothes. Details of an order include date of the order and quantity of the clothes s/he orders. A customer can place several orders on a day and the details of each order need to be kept.
• Some customers are VIP customers who are issued a membership number. While a VIP customer is serviced by one service specialist (who is an employee). A service specialist cares for several VIP customers.
• VIP events about brands are organised regularly, including details such event date and format. A brand can be included in several events and an event can promote several brands. A service specialist promotes events to his/her VIP customers.
Examples of queries required by 3S:
• Look up the details of suppliers, service specialists, customers and clothes for daily operation.
• Check the stock quantity of clothes and contact suppliers for a new supply if needed.
• Find out details of events for different brands.
According to the data requirements and query requirements above, give the ER diagram for the database using the UML class diagram symbols (as used in the lecture notes and tutorials), making appropriate assumptions where necessary. You must represent entity types, relationships and their attributes, and all applicable constraints in your diagram. Explain any constraints that are not be expressed in the diagram. Note that your ER diagram would be mapped to a relational database schema and implemented as tables using a DBMS. It may be helpful that you do this mapping and see if the queries can be answered using the mapped relations to help you refine the ER diagram.
Question 4. ER to Relational schema Mapping (6 points).
Consider the ER diagram for the Beautiful House Real Estate Agency database below using UML class diagram symbols. Map the diagram to a relational database schema. Indicate the primary key (underline) and any foreign keys (asterisk) for each relation. Note that a relational database schema comprises a set of relations written in the form as shown in the examples below:
Student(sno, name, address)
Course(cno, title)
Take(sno*, cno*, grade)
<a class=”btn btn-outline-primary w-100 btn-lg” href=””>Order Now</a>

Calculate a fair price for your paper

Such a cheap price for your free time and healthy sleep

1650 words
Place an order within a couple of minutes.
Get guaranteed assistance and 100% confidentiality.
Total price: $78
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?