ISYS1055 Database Concepts Assessment 1 – RMIT University Australia.

Subject Code & Title :- ISYS1055 Database Concepts
Assignment type :- Assessment 1
Weighting:- 20%
Word limit: N/A
Overview :- The objective of this assignment is to measure your understanding of the basic concepts in the relational database model and using entity-relationship model for database design. The assessment is in two parts split into four tasks which cover Basic ER Modelling and Basic Relational Modelling. The tasks are as follows.
ISYS1055 Database Concepts Assessment 1 – RMIT University Australia.

Part A: Entity-Relationship Modelling
1.Design and plan for the implementation of a database system diagramming the design to a high standard using UML notation through the diagramming tool Lucid chart.

2.Model the activities of an organisation and present the model as an Entity-Relationship (ER) diagram. Analyse this ER diagram, and possibly modify it, based on additional client requirements.

3.Map an ER diagram into a relational database schema, showing every step of the mapping.

Part B: Relational Database Model

4.Answer a series of short questions about a Relational Database model.

To complete this assessment you must be familiar with Lucidchart, which is covered during the Week 1-4 activities.

Assessment criteria :-
This assessment will measure your ability to:
• Describe various data modelling and database system technologies.
• Explain the main concepts for data modelling and characteristics of database systems.

Course learning outcomes :-
This assessment is relevant to the following course learning outcomes:

CLO1 Describe the underlying theoretical basis of the relational database model and apply the theories into practice.
CLO2 Explain the main concepts for data modelling and characteristics of database systems.
CLO3 Develop a sound database design using conceptual modelling mechanisms such as entity-relationship diagrams.
CLO4 Develop a database based on a sound database design.

Part A: Entity-Relationship Modelling
Task 1: Designing an Entity-Relationship Model

Food Saver Case Study
Food Saver (FS) is a supermarket that sells a variety of food products in Australia. The following are the requirements for managing data about staff, products and customers for FS.

Stores :-
FS has stores throughout Australia. Each store is allocated several staff members. One staff member manages the operations of the store. Each staff member works at a specific store only. The data describing a store includes a unique store number an address (i.e. street suburb and postcode) and several contact types (e.g., email fax phone). FS keeps track of which staff members work at each store including the manager (and the date each staff member starts his/her position at the store he/she is working at).

Staff :-
It is important to distinguish between two types of FA staff members (i.e., supervisors and workers) as supervisors have additional responsibilities. Supervisors are responsible for the day-to-day activities of a
team of workers.Each supervisor is responsible for all staff at a particular store. The data for each staff
member includes a unique staff number, name, address, position, and salary.

Products :-
FS has a range of products that it sells to customers in Australia. At any given time at a store a particular
product has a specific quantity in stock which must be tracked. Other data for each product includes a
unique product number a name description and sale price.

Customers :-
When customers register on the FS website, the system records their unique email address their name
delivery address (i.e., street, suburb, and postcode) and payment details (Credit Card number expiry date and security code).

Orders :-
Once registered customers can order products from the website selecting the product and quantity to be added to their order. Each order has a unique order number and order date.

Based on the given description, model the given business rules and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram you must properly denote all applicable concepts including weak or strong entities keys composite or multi-valued attributes relationships and their cardinality and participation constraints.

If you cannot represent any of this information in the ER model clearly explain what limitations in the ER model restrict you from representing your model.

You must use UML notation and the diagramming tool Lucid chart to draw your diagram. Your diagram must be drawn to a high standard with minimal clutter. You are not required to map the ER model to relational model.

A special note: This is an open-ended question with many different models that can be derived. Your model is assessed based on how accurately it represents business rules described above.

Task 2: Designing an Entity-Relationship Model

Part A: Initial Design

Sunny Holidays Case Study
Sunny Holidays is an Australian travel company that allows customers to book holiday packages.You are asked to design a database for managing customer bookings. Requirements for the database are as follows:

• Customers book holiday packages recording the date the booking was made. Each customer has a
first name last name date of birth and passport number.
• Holiday packages have a package no and have a set fee (depending on the departure date). They
consist of several stop overs each in a city within a country. Each country could have several stop
over cities.
• Each stop over has a sequence number (eg stop number 1, 2 etc).
• The system stores a complete list of cities and countries some which may not necessarily have an associated holiday stop over (yet).
• All holiday packages have at least one stopover.

Based on the given description model the business rules of Sunny Holidays and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram you must properly denote all applicable concepts, including weak or strong entities keys composite or multi-valued attributes relationships and their cardinality and participation constraints.

If you cannot represent any of this information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model.

You must use UML notation and the diagramming tool Lucid chart to draw your diagram. Your diagram must be drawn to a high standard with minimal clutter. You are not required to map the ER model to relational model.

A special note: This is an open-ended question with many different models that can be derived. Your model is assessed based on how accurately it represents business rules described above.

Part B: Client Adjustments
After presenting your ER model to Sunny Holidays management, you are asked if it can be used to perform the following additional tasks.

• Customers arrive at and depart from each stop over on a flight at a specific date time.
• Flights have a unique code and also a duration.
• Depending on the duration of the stop over, there may be a single nominated accommodation.
• Accommodation has an identifying address and has a phone number.
• The check in and check out date time is recorded for each accommodation stay.

For each one of the tasks specified above, explain how your ER diagram is supporting it. If it is not possible to achieve any of the tasks above given your current design state why modify the model provide the modified ER diagram (in addition to your original ER diagram) and explain how the new model achieves the missing requirements.

Task 3: Mapping an ER Model to a Relational Database Schema
Consider the following ER diagram which shows aspects of the business of a computer service company.

You are requested to map the above ER diagram into a relational database schema. Show every step of the mapping.No marks are awarded to the final schema if you do not show the partially built schema at the end of each step.Clearly indicate the primary key (underlined) and foreign keys (with an asterisk) in each relation.

Part B
Task 4: Relational Database Model
This section contains the schema and a database instance for the Employee database that stores employee data for an organisation. The data includes items such as personal info (e.g., name phone
salary) departments of the organisation (e.g., name and location of each department who the manager
is) jobs (e.g., titles, salary range) and a history for past contracts with each employee. A database instance is shown in Figure 2 followed by the database schema.

The database schema is shown below, and the meaning of most attributes is self-explanatory. “Job History” is simplified as “Job History”. Primary keys are underlined, and foreign keys are annotated with a *.

Employees(employee_id first_name last_name, phone_number hire_date
emp job_id salary department_id) Departments(department_id department_name manager_id location_id)
Jobs(job_id, job_title, min_salary, max_salary)Locations(location_id street_address postal_code city state_province country_id) Countries(country_id, country_name) Job History(employee_id start_date end_date job_id department_id)

The following table further clarifies the connection between the keys across multiple tables.

The following questions must be answered based on the given database schema and instance. Where explanation is required each answer should be a SHORT passage of at most several lines.

QUESTION 4.1: Does the database schema ensure that there is a department associated with each employee? Explain your answer.

ISYS1055 Database Concepts Assessment 1 – RMIT University Australia.

Questions 4.2: What change (if any) would you need to make so that the database can store multiple job histories for the same employee with the same start and end dates but with different titles?

QUESTION 4.3: The Human Resource department has recently changed to have three sub-departments (i.e., On going Staff Department Casuals Department and External Contractors Department). Now each sub-department is supposed to have a separate manager. Additionally the managers of all Human Resource sub-departments now must report to a single Director (i.e., “Director of Human Resources”). Temporarily and until the new managers are hired Joseph has been appointed to the management of all three sub-departments as well to the single role of Director of Human Resources.

The following SQL statements are intended to record all the changes required in the database instance. Will they work? If they are sufficient to achieve the requirements specified above explicitly mention they are sufficient. If there are any shortcomings identify them and briefly justify your answer.

ISYS1055 Database Concepts Assessment 1 – RMIT University Australia.

UPDATE Departments SET department_name=’Human Resource – Ongoing Staff’ WHERE
department_id=3;
INSERT INTO Departments VALUES(4, ‘Human Resource – Casual’, 18, 30);
INSERT INTO Departments VALUES(5, ‘Human Resource – Exteral Contractors’, 18, 30);

QUESTION 4.4: The employee named Adam Smith has recently change his job to become a Programmer. The following SQL statement intends to make the required changes in the database instance to reflect Adam’s promotion.

UPDATE Employees SET empjob_id=33 WHERE first_name,last_name=’Adam Smith’;

ISYS1055 Database Concepts Assessment 1 – RMIT University Australia.

After running the above query consider the request “find all the past contracts that Adam Smith used to have”. Can this request be completed using the given database schema and after the above statement is run? If yes explain how the request can be answered. If no, explain what is missing and how it should be fixed.

QUESTION 4.5: Explain what the result of executing the following SQL statement on the database instance will be.

UPDATE LOCATIONS SET location_id=40 WHERE location_id=30;

QUESTION 4.6: Write an SQL statement to create the Locations table including all the constraints assuming all the tables that Locations depends on already exist in the database. Make reasonable assumptions for the data type associated with each field. Your SQL statement must be valid for SQLite Studio environment and free of any errors.

ISYS1055 Database Concepts Assessment 1 – RMIT University Australia.

QUESTION 4.7: Write an SQL statement to create the Job History table including all the constraints, assuming all the tables that Job History depends on already exist in the database. Make reasonable assumptions for the data type associated with each field. Your SQL statement must be valid for SQLite Studio environment and free of any errors.

QUESTION 4.8: A new branch is opening up in Spain. You are asked to update the given database instance so that it includes the new Location. The location ID is “40”, the street address is “666 Diablo st”, the postcode is “46001” the city is “El Carmen” the state_province is “Valencia”. Your SQL statement must be valid for SQLite Studio environment free of any errors, and compatible/consistent with existing data in the instance in Figure 2.