ASSESSMENT TASK 1A: PORTFOLIO A
OVERVIEW
This assessment task focuses on the following learning outcomes:
- Describe components of a database system, advantages and disadvantages of a database system, roles people play and the historical development of a database system in the context of a Relational Database Management System (RDBMS).
- Apply basic skills in database modelling, including ER diagrams and normalisation in RDBMS. This Portfolio is divided into two parts. You need to complete both parts:
Part I: Theory related questions Part II: Case studies.
DETAILS
| Weighting | 15% |
| Type of collaboration | Individual |
| Due dates | Week 2 — Sunday 11.59 pm |
| Submission | Online via vUWS |
| Format | MS Word file with coversheet. Part 1 and II to be submitted.
ER diagrams are to be drawn digitally using an appropriate tool and incorporated into a Word document. |
| Length | 500 words |
| Learning outcomes addressed | 1, 2 |
PART I: THEORY RELATED QUESTIONS
- Access the website https://seek.com.au and answer the following questions
- Fill in the table below giving the database related jobs and recent trends details.
| Item
(This could be a role or a recent trend–based role in database technologies) |
How many jobs were found?
Mention the date on which search was done. |
What was the highest salary amongst the jobs found? | List the key requirements of the role. |
| Database administrator |
39 21/10/2022 |
$200 000 PA | • Influencing IT and business stakeholders for strategic outcomes.
• Assessing new technologies/solutions that support our strategic aims for usage by the data and analytics community • Innovation that supports health services outcomes, reduces costs and benefits customers • Utilizing the value of the wider Medibank technology investments through the identification and implementation of innovative solutions to business data requirements • Working closely with enterprise architects to ensure data is fully integrated into our landscape • Supporting solution delivery through estimation and guidance for excellent project delivery • Architect and design data platforms align with Medibank enterprise architecture and security guidelines |
| Logical database designer | 0
21/10/2022 |
$87051.00 PA | “The logical database designer is concerned with identifying the data (that is, the entities and attributes), the relationships between the data, and the constraints on the data that is to be stored in the database”
Roles in DBMS Environment, DBA Role, Database Designers – Iravati Solutions |
| Physical database designer | 0
21/10/2022 |
$87051.00 PA | The physical database designer decides how the logical database design is to be physically realized.
Roles in DBMS Environment, DBA Role, Database Designers – Iravati Solutions |
| Application Developer | 1
22/10/2022 |
$140 000.00 to $180 000 PA |
seek.com.au |
| End-users | 18
23/10/2022 |
Up to $350 000 PA |
Deployment
Seek.com.au |
| Data warehouse | 1532
23/10/2022 |
$108,669 to $123,939 |
Seek.com.au |
| Data mining | 332
24/10/2022 |
$140 000-00 to $150 000-00 PA |
Seek.com.au |
| Cloud database | 130
24/10/2022 |
$100 000-00 to $130 000-00 |
Seek.com.au |
| Item
(This could be a role or a recent trend–based role in database technologies) |
How many jobs were found?
Mention the date on which search was done. |
What was the highest salary amongst the jobs found? | List the key requirements of the role. |
| Mongo DB | 13
24/10/2022 |
$130 000-00 to $150 000-00 |
Seek.com.au |
| Hadoop | 9
24/10/22 |
$110 000-00 to $140 000-00 PA | xtensive experience providing practical direction within the Hadoop Distribution domain
Ability to provide direction specific to enterprise needs Experience standing up and using Big Data environments to implement data analytics Define technical requirements, technical and data architectures for the big data ecosystem on AWS Ingest and transform data from multiple sources Work with vendor platform providers and engineering peers to keep abreast of trends, products, frameworks, and applications. Experience with big data ecosystem and tools engineering, development, operations Strong understanding of operating platform stacks including Redhat Linux, Windows, OpenStack. Experience in developing strategies, roadmaps and designs for large-scale organizations. Enabling the enterprise the ability for rapid growth and scalable solutions that do not require extensive manual intervention. In depth knowledge of scripting tools and configuration management software (Python, Powershell, Perl, Saltstack, etc..) to enable extensive automation of our products and technologies for provisioning and management of the systems. Effectively identify and manage stakeholder engagement and impacts across the enterprise Engage executive stakeholders appropriately to review progress and obtain input, validation and approval of key decisions Proven ability to collaborate across a large organisation to effectively realise outcomes Seek.com.au |
| Spark | 761
24/10/2022 |
$150 000-00 to $160 000-00 PA | Investigate enterprise data requirements.
Plan and executed data modelling and design activities. Apply standards to data architecture, security & compliance.
Plan & deliver data engineering solutions. Ensure data solutions meet requirements. Identify & evaluate available architectures and their trade-offs in cost, performance and scalability.
Master data management solutions. Profiling data integrity. Enable data discoverability & metadata.
Seek.com.au |
- Identify a role that interests you. Explain why this role is of interest.
Database Administrator. I would like to pursue a career in this field because I find it interesting and challenging.
advantage “Better Data Security” disadvantage “Increased cost of storage space”
- with the help of an example scenario, explain how your listed advantage is an advantage.
If there is a cyberattack, the tools, methodologies and processes maintains the security within the database environment.
- file-based systems: It is a collection of programs that manage and store data in files and folders on a computer hard disk. And RDBMS: RDBMS uses normalization to eliminate data redundancy.
Describe components of a DBMS: Hardware, Software, Data, Database Access Language, Procedures and Users
PART II: CASE STUDIES
- Business scenario for the College Accommodation Management System
- The director of the Western College Accommodation Office requires you to design a database to assist with the administration of The College Residences. The requirements collection and analysis phase of the database design process has provided the following data requirements specification for the Western College Accommodation Office database.
Data requirements
The data stored for each Student includes name (first name, family name), home address (street, town, postcode), contact phone number, email address, date of birth, gender, category (university preparation, undergraduate, postgraduate), and status (waiting, resident).
The accommodation available is organised into residences, each of which has a unique name, address, telephone number, and a Hall Manager who supervises the operation of the residence. Each residence contains several rooms.
Each room is assigned a unique room number and the cost of occupancy for a semester is recorded for each room. The room number uniquely identifies each room in all residences controlled by the Accommodation Office and is used when renting a room to a student.
Each room is inspected on a random basis; data held about each Inspection includes the date of inspection, name of the staff member who carried out the inspection, inspection outcome (satisfactory, unsatisfactory) and general comments.
At the start of each semester, each student accepted into a residence is assigned a room for that semester — this constitutes the Occupancy record. It is necessary to maintain an historical record of occupancies, that is, to be able to tell who occupied a room between specified dates.
Each semester, every resident student is issued with an Invoice related to their occupancy of a room, detailing the date the invoice was issued, and cost for the semester. Recording payment of the invoice is NOT part of this system.
Using the information supplied, represent the data requirements for your logical database schema in a single entity-relationship diagram (ER diagram). Your ER diagram should:
- identify the main entity types:
- identify attributes and associate them with entity or relationship types; represent each attribute in your ER diagram
- identify the main relationship types between the entity types identified in part (a) and represent each relationship on your ER diagram
- show the primary and foreign key attributes for each entity, and
- show the multiplicity constraints for each relationship identified in part (c) using either crow’s foot or UML notation.
Note: ER diagrams can be drawn by using a variety of tools (eg, Microsoft Visio/Lucid chart/draw.io/Microsoft Word). With some applications, you may wish to take screenshots of your ER diagrams and insert them into your Word document
- Business scenario for the Youth League Management System
The local city Youth League needs a database system to help track children who sign up to play soccer. Data needs to be kept on each team, the children who will play on each team, and parent details. Also, data needs to be maintained on the coaches for each team and matches.
You need to store the Team ID, Team name, Player ID, Player first name, Player last name, and Player age for all players. Each team may or may not have a player. A player must have a team and should belong to only one team. You also need to store the Coach ID, First name of the coach, Last name of the coach, Home phone number of the coach.
A Team may have many coaches or may not have a coach. A coach must have only one team assigned to him or her. For each parent, the Parent’s ID, their Last name, First name, Home phone number and Home address need to be kept in the system.
A player may have more than one parent. A parent may have only one player. Teams play matches. For each match we need to keep track of the following: Match ID, the Date on which the game is played, the Result of the match, Host team ID (HID) and Guest team ID (GID).
Normalise these relations to 3rd normal form, ensuring that the resulting relations are dependency preserving. Explain each step clearly along with any assumptions you may have made.
SUBMISSION GUIDELINES
- You are to submit your portfolio via the Assessment 1A: Portfolio A link on vUWS.
- Name the Word file to be submitted in the following form: StudentIDFirstPortfolioA, eg 18325845RichardPortfolioA.
MARKING GUIDE
| Criteria | Fail 0–49% | Pass 50–64% | Credit 65–74% | Distinction 75–84% | High distinction 85–100% |
| Demonstrates knowledge of the roles people play
(20 marks) |
Table not completed or the answer does not show understanding of all of the following:
with more than three cell values that do not relate to the corresponding item. Does not identify the role that is of personal interest and/or does not justify their choice clearly. |
All components of the table completed except that three cell values do not relate to the corresponding item.
Identifies the role that is of personal interest and provides justification of their choice. |
All components of the table completed except that two cell values do not relate to the corresponding item.
Identifies the role that is of personal interest and provides justification of their choice. |
All components of the table completed except that one cell value does not relate to the corresponding item.
Identifies the role that is of personal interest and provides justification of their choice. |
All components of the table are completed.
Identifies the role that is of personal interest and provides justification of their choice. |
| Identifies advantages and disadvantages of a database system
(10 marks) |
Does not describe an advantage and/or a disadvantage of a DBMS.
Does not describe a relevant scenario. |
Identifies an advantage and a disadvantage of a DBMS, and describes a scenario but does not explain how the feature is an advantage or the feature is not depicted well in the scenario. | Identifies and describes a key advantage and a disadvantage of a DBMS, and describes a scenario explaining how the feature is an advantage. | Identifies and describes a key advantage and a disadvantage of a DBMS, and describes a scenario explaining convincingly how the feature is indeed an advantage. | Identifies and describes a key advantage and a disadvantage of a DBMS, and describes a scenario explaining convincingly and succinctly how the feature is indeed an advantage. |
| Differentiate between RDBMS and file-based (traditional) systems
(15 marks) |
Does not describe components of a DBMS and how they relate to one another and/or does not differentiate between file- based systems and RDBMS. | Describes some components of a DBMS, but does not describe the relationship between the components clearly.
Describes some features of file- based systems and RDBMS but does not explain the distinguishing features clearly. |
Describes the components of a DBMS without the relationship between the components being clearly portrayed, and explains at least two points of difference between file-based systems and RDBMS. | Describes the components of a DBMS and the relationship between the components clearly, and explains at least three points of difference between file-based systems and RDBMS. | Describes the components of a DBMS and the relationship between the components clearly and effectively, and explains at least four points of difference between file-based systems and RDBMS. |
© Western Sydney University Enterprises Pty Limited 2022 17
| Criteria | Fail 0–49% | Pass 50–64% | Credit 65–74% | Distinction 75–84% | High distinction 85–100% |
| Apply basic skills in database modelling including ER diagrams
(35 marks) |
The logical database schema includes some entities and attributes but does not model the database effectively to solve the given problem. Most primary keys, foreign keys and constraints are not identified. The model is not presented using correct notation. | The logical database schema includes some of the essential entities with some attributes to model the solution database with meaningful relationships. Most primary keys, foreign keys and constraints are identified correctly. The model is presented using correct notation. | The logical database schema includes the essential entities with sufficient attributes to model the solution database with meaningful relationships. Primary keys, foreign keys and constraints are identified correctly. The model is presented using correct notation and is easy to comprehend. | The logical database schema includes most of the enhanced entities with their required attributes in an ER diagram that models the solution database with meaningful relationships. Primary keys, foreign keys and constraints are identified correctly. The model is designed effectively, is presented using correct notation and is easy to comprehend. | The logical database schema includes all the enhanced entities with their required attributes in an ER diagram that models the solution database with meaningful relationships. Primary keys, foreign keys and constraints are identified correctly. The model is designed efficiently, is presented using correct notation and is easy to comprehend. |
| Normalisation in RDBMS
(20 marks) |
The database is unnormalised and/or no steps clearly mentioned. | The tables are normalised only to 1NF mentioning all steps, ensuring the resulting relations are dependency preserving. | One table is normalised to 2NF and all others to 1 NF mentioning all steps, ensuring the resulting relations are dependency preserving. | One table is normalised to 3NF and all others to 2 NF mentioning all steps, ensuring the resulting relations are dependency preserving. | The database is normalised to 3NF ensuring the resulting relations are dependency preserving. |
| Total marks: 100 |
© Western Sydney University Enterprises Pty Limited 2022 18
