微信客服：xiaoxionga100
微信客服：ITCS521
数据库代写-ISYS3412
时间：2021-05-10
Page 1 of 6
RMIT Classification: Trusted
School of Computing Technologies
ISYS3412 Practical Database Concepts/ISYS1055 Database Concepts
Assessment 3
Assessment Type: Individual assignment. Submit online via Canvas→Assignments→Assessment 3. Marks are awarded for meeting requirements as closely as possible. Clarifications/updates may be
made via announcements/relevant discussion forums.
Due time: 23:59 17 May 2021, Monday Week 11.
Weighting: 20 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 assessment you will apply the skills and concepts that
you have learned about database systems in the course.
2. Assessment Criteria
This assessment will determine your ability to:
1. Independently design a database using the ER model.
2. Map a given ER model into a relational database schema.
3. Relational database schema normalisation.
Ask questions, when needed, via Canvas discussion forums.
This assignment is worth 20 points, which accounts for 20% of the overall assessment for the course:
Assessment 1 Assessment 2 Assessment 3 Assessment 4
10% 30% 20% 40%
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: 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
Submit your assignment via Canvas→Assignments→Assessment 3. You are required to submit a pdf file. The
file must be named after your student number -- for example, S1234.pdf.
• 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. However, if your last submission is after the due time, late penalties apply.
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:
Page 2 of 6
RMIT Classification: Trusted
• 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
Failure to submit files in the required format results in a penalty of 10 marks. Examples include the
submission not in PDF, ER diagrams not using notations in the lecture slides.
Late submission results in a penalty of 2 marks for (up to) each 24 hours being late. Submissions more than
5*24 hours late results in zero marks.
If unexpected circumstances – for example unexpected short-term physical or mental ill-health -- affect your
ability to complete the assignment, you may be eligible for assessment adjustment:
• Requests for extension of time for submission of up to 7 days can be made by emailing the course
coordinator. Application form available at:
https://www.rmit.edu.au/students/student-essentials/assessment-and-results/extensions-of-time-for-
submission-of-assessable-work
• Otherwise apply for special consideration via the University Special consideration:
https://www.rmit.edu.au/students/student-essentials/assessment-and-exams/assessment/special-
consideration.
Special Consideration that extends beyond the release of solutions (typically 2 weeks from the submission due
date) 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).
Page 3 of 6
RMIT Classification: Trusted
Marking rubric
Criteria Marks
Question 1.1
(3 marks)
Each major
error -1 pt and
minor error -0.5
pts.
0-1pts
Many (>=2) major errors;
examples: wrong NF,
missing/wrong explanation,
missing/wrong annotation for
PK or FK.
2 pts
Either of below is met:
* Some (<=1) major error.
* Minor errors; examples:
not completely correct
explanation but shows
understanding of the NF
definition.
3 pts
Mostly correct and
possible (at most one)
very minor error (e.g.
wrong attribute name).
Question 1.2
(3 marks)
Major error -1 pt
and minor error
-0.5 pts.
0-1 pts
Either of below is met:
* No or incorrect working is
shown.
* Many (>=2) major errors in
the resultant relation schemas;
examples: wrong, missing or
redundant resultant relation
schemas, or missing or wrong
PK or FK annotations.
2 pts
ALL of below is met:
* Correct working is
shown.
* Some (<=1) major errors
in the resultant relation
schemas.
3 pts
ALL of below is met:
* Correct working is
shown.
* Mostly correct resultant
relation schemas and
possible (<=1) very
minor error (e.g. wrong
attribute name).
Question 2
(8 marks)
Major error -1 pt
and minor error
-0.5 pts.
0-3 pts
Either of below is met:
* Missing, redundant, or wrong
modelling for many (>=3)
required concepts (entity sets
or relationships).
* Many (>=3) major errors.
Examples: redundant entity
sets/relationships.
* Most (>=3) example queries
can not be answered with the
ER model.
* Many (>=3) major errors;
examples: missing PK,
redundant entity
sets/relationships.
4-6
Either of below is met:
* Missing, redundant, or
wrong modelling for some
(<=2) required concepts.
* Some (<=2) major errors.
* Some (<=2) example
queries can not be
answered with the ER
model.
* Some (<=2) major errors.
7-8 pts
ALL of below must be
met:
* All required concepts
are modelled.
* All example queries
can be answered.
* Possible minor errors (-
0.5 pts); examples:
missing non-key
attribute, missing/wrong
cardinality for
relationships.
Question 3
(6 marks)
Major error -1 pt
and minor error
-0.5 pts.
0-2 pts
Either of below is met:
* Missing, redundant, or wrong
mapping for many (>=3)
concepts (entity sets or
relationships).
* Many (>3) major errors;
examples: missing/wrong
primary/foreign key attributes
or their annotation.
3-4 pts
Either of below is met:
* Missing, redundant, or
wrong mapping for some
(<=2) concepts.
* Some (<=2) major errors.
5-6 pts
ALL of below must be
met:
* Correct mapping for all
concepts
* No major errors.
* Possible minor errors;
examples: wrong spelling
attribute names.
Page 4 of 6
RMIT Classification: Trusted
8. Assignment questions.
Question 1. Normalisation (6 points).
Consider the following relational database schema about employees from departments working on projects:
Employee(empNo, givename, surname, DOB, gender, deptNo, dept_name)
Department(deptNo, dept_name, location, manager)
WorkOn(empNo, projNo, proj_name, deptNo, hours)
Some notes on the semantics of attributes are as follows:
• Each employee has a unique employee number (empNo) and work for a department.
• Each department has a unique department number (deptNo), a manager and possibly several
locations.
• Each project has a unique project number (projNo). A project also has an “owning” department.
• Employees work on projects for some hours.
FDs based on business rules are given as follows:
• empNo à givename, surname, DOB, deptNo, dept_name
• deptNo à dept_name, manager
• projNo à proj_name, deptNo, dept_name
• empNo, projNo à proj_name, deptNo, hours
Answer questions below:
1.1 (3 points) Identify and annotate the primary key (underline) and any foreign keys (asterisk) for each given
relation. Give the highest normal form for each relation. Explain your answer using the given FDs. 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)
1.2 (3 points) Decompose any given relation not in BCNF or 3NF into relations in BCNF or 3NF. Your
decomposition must keep all functional dependencies and must be lossless. Show your working for the
decomposition. For each resultant relation, explain if it is in BCNF or 3NF and annotate the primary key
(underline) and any foreign keys (asterisk).
Page 5 of 6
RMIT Classification: Trusted
Question 2. The ER Model (8 points).
The XB Records management has decided to build a database storing information about artists who perform
for their albums. The company has hired you to design the database. Description of the database is as
follows.
• Each artist has a unique ID, a name, and an address.
• A band has a name, year established and place of origin. A band has several artists. An artist usually is
a member of only one band at a time.
• Each song has an ID and a title.
• Genres of songs are described by a unique name, some description. For example, “rock” is a genre
with the description “rock music originated as ‘rock and roll’ in the United States”. A genre may have
many songs.
• Each album recorded on the XB label has a title, a release date, a format (such as CD, MP3), and a
unique album number. Each album has many tracks on it (up to 20 usually), and they are numbered
sequentially. Each track of an album usually corresponds to a song.
• An album may be by an artist or a band (but not both).
• A song may appear on several albums. A song has one or more genres, and a genre may have many
songs.
Examples for queries on the database:
• Look up the details of artists, bands, songs and albums.
• List the songs and their track number for the album “Thriller” (title).
• Give the number of songs for each genre.
• Find the albums by an artist named “ Bob Janes”.
• Give the name of the band for the album “The Last Waltz”.
• How many albums have the song “Almost is Never Enough”?
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 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.
Page 6 of 6
RMIT Classification: Trusted
Question 3. ER to Relational Schema Mapping (6 points).
Consider the ER diagram for a Software Testing 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 relation schemas written in the
form as shown in the examples below:
Student(sno, name, address)
Course(cno, title)
Take(sno*, cno*, grade)
学霸联盟
RMIT Classification: Trusted
School of Computing Technologies
ISYS3412 Practical Database Concepts/ISYS1055 Database Concepts
Assessment 3
Assessment Type: Individual assignment. Submit online via Canvas→Assignments→Assessment 3. Marks are awarded for meeting requirements as closely as possible. Clarifications/updates may be
made via announcements/relevant discussion forums.
Due time: 23:59 17 May 2021, Monday Week 11.
Weighting: 20 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 assessment you will apply the skills and concepts that
you have learned about database systems in the course.
2. Assessment Criteria
This assessment will determine your ability to:
1. Independently design a database using the ER model.
2. Map a given ER model into a relational database schema.
3. Relational database schema normalisation.
Ask questions, when needed, via Canvas discussion forums.
This assignment is worth 20 points, which accounts for 20% of the overall assessment for the course:
Assessment 1 Assessment 2 Assessment 3 Assessment 4
10% 30% 20% 40%
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: 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
Submit your assignment via Canvas→Assignments→Assessment 3. You are required to submit a pdf file. The
file must be named after your student number -- for example, S1234.pdf.
• 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. However, if your last submission is after the due time, late penalties apply.
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:
Page 2 of 6
RMIT Classification: Trusted
• 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
Failure to submit files in the required format results in a penalty of 10 marks. Examples include the
submission not in PDF, ER diagrams not using notations in the lecture slides.
Late submission results in a penalty of 2 marks for (up to) each 24 hours being late. Submissions more than
5*24 hours late results in zero marks.
If unexpected circumstances – for example unexpected short-term physical or mental ill-health -- affect your
ability to complete the assignment, you may be eligible for assessment adjustment:
• Requests for extension of time for submission of up to 7 days can be made by emailing the course
coordinator. Application form available at:
https://www.rmit.edu.au/students/student-essentials/assessment-and-results/extensions-of-time-for-
submission-of-assessable-work
• Otherwise apply for special consideration via the University Special consideration:
https://www.rmit.edu.au/students/student-essentials/assessment-and-exams/assessment/special-
consideration.
Special Consideration that extends beyond the release of solutions (typically 2 weeks from the submission due
date) 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).
Page 3 of 6
RMIT Classification: Trusted
Marking rubric
Criteria Marks
Question 1.1
(3 marks)
Each major
error -1 pt and
minor error -0.5
pts.
0-1pts
Many (>=2) major errors;
examples: wrong NF,
missing/wrong explanation,
missing/wrong annotation for
PK or FK.
2 pts
Either of below is met:
* Some (<=1) major error.
* Minor errors; examples:
not completely correct
explanation but shows
understanding of the NF
definition.
3 pts
Mostly correct and
possible (at most one)
very minor error (e.g.
wrong attribute name).
Question 1.2
(3 marks)
Major error -1 pt
and minor error
-0.5 pts.
0-1 pts
Either of below is met:
* No or incorrect working is
shown.
* Many (>=2) major errors in
the resultant relation schemas;
examples: wrong, missing or
redundant resultant relation
schemas, or missing or wrong
PK or FK annotations.
2 pts
ALL of below is met:
* Correct working is
shown.
* Some (<=1) major errors
in the resultant relation
schemas.
3 pts
ALL of below is met:
* Correct working is
shown.
* Mostly correct resultant
relation schemas and
possible (<=1) very
minor error (e.g. wrong
attribute name).
Question 2
(8 marks)
Major error -1 pt
and minor error
-0.5 pts.
0-3 pts
Either of below is met:
* Missing, redundant, or wrong
modelling for many (>=3)
required concepts (entity sets
or relationships).
* Many (>=3) major errors.
Examples: redundant entity
sets/relationships.
* Most (>=3) example queries
can not be answered with the
ER model.
* Many (>=3) major errors;
examples: missing PK,
redundant entity
sets/relationships.
4-6
Either of below is met:
* Missing, redundant, or
wrong modelling for some
(<=2) required concepts.
* Some (<=2) major errors.
* Some (<=2) example
queries can not be
answered with the ER
model.
* Some (<=2) major errors.
7-8 pts
ALL of below must be
met:
* All required concepts
are modelled.
* All example queries
can be answered.
* Possible minor errors (-
0.5 pts); examples:
missing non-key
attribute, missing/wrong
cardinality for
relationships.
Question 3
(6 marks)
Major error -1 pt
and minor error
-0.5 pts.
0-2 pts
Either of below is met:
* Missing, redundant, or wrong
mapping for many (>=3)
concepts (entity sets or
relationships).
* Many (>3) major errors;
examples: missing/wrong
primary/foreign key attributes
or their annotation.
3-4 pts
Either of below is met:
* Missing, redundant, or
wrong mapping for some
(<=2) concepts.
* Some (<=2) major errors.
5-6 pts
ALL of below must be
met:
* Correct mapping for all
concepts
* No major errors.
* Possible minor errors;
examples: wrong spelling
attribute names.
Page 4 of 6
RMIT Classification: Trusted
8. Assignment questions.
Question 1. Normalisation (6 points).
Consider the following relational database schema about employees from departments working on projects:
Employee(empNo, givename, surname, DOB, gender, deptNo, dept_name)
Department(deptNo, dept_name, location, manager)
WorkOn(empNo, projNo, proj_name, deptNo, hours)
Some notes on the semantics of attributes are as follows:
• Each employee has a unique employee number (empNo) and work for a department.
• Each department has a unique department number (deptNo), a manager and possibly several
locations.
• Each project has a unique project number (projNo). A project also has an “owning” department.
• Employees work on projects for some hours.
FDs based on business rules are given as follows:
• empNo à givename, surname, DOB, deptNo, dept_name
• deptNo à dept_name, manager
• projNo à proj_name, deptNo, dept_name
• empNo, projNo à proj_name, deptNo, hours
Answer questions below:
1.1 (3 points) Identify and annotate the primary key (underline) and any foreign keys (asterisk) for each given
relation. Give the highest normal form for each relation. Explain your answer using the given FDs. 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)
1.2 (3 points) Decompose any given relation not in BCNF or 3NF into relations in BCNF or 3NF. Your
decomposition must keep all functional dependencies and must be lossless. Show your working for the
decomposition. For each resultant relation, explain if it is in BCNF or 3NF and annotate the primary key
(underline) and any foreign keys (asterisk).
Page 5 of 6
RMIT Classification: Trusted
Question 2. The ER Model (8 points).
The XB Records management has decided to build a database storing information about artists who perform
for their albums. The company has hired you to design the database. Description of the database is as
follows.
• Each artist has a unique ID, a name, and an address.
• A band has a name, year established and place of origin. A band has several artists. An artist usually is
a member of only one band at a time.
• Each song has an ID and a title.
• Genres of songs are described by a unique name, some description. For example, “rock” is a genre
with the description “rock music originated as ‘rock and roll’ in the United States”. A genre may have
many songs.
• Each album recorded on the XB label has a title, a release date, a format (such as CD, MP3), and a
unique album number. Each album has many tracks on it (up to 20 usually), and they are numbered
sequentially. Each track of an album usually corresponds to a song.
• An album may be by an artist or a band (but not both).
• A song may appear on several albums. A song has one or more genres, and a genre may have many
songs.
Examples for queries on the database:
• Look up the details of artists, bands, songs and albums.
• List the songs and their track number for the album “Thriller” (title).
• Give the number of songs for each genre.
• Find the albums by an artist named “ Bob Janes”.
• Give the name of the band for the album “The Last Waltz”.
• How many albums have the song “Almost is Never Enough”?
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 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.
Page 6 of 6
RMIT Classification: Trusted
Question 3. ER to Relational Schema Mapping (6 points).
Consider the ER diagram for a Software Testing 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 relation schemas written in the
form as shown in the examples below:
Student(sno, name, address)
Course(cno, title)
Take(sno*, cno*, grade)
学霸联盟
