Reference no: EM133136321

IFB105 Database Management - Queensland University of Technology

Project - database design

In this assessment you will design a database by:
- Designing an ORM conceptual schema for a business domain based on a report for that domain.
- Map a different ORM schema to a relational database schema.

Rationale and Description

Understanding, designing, and managing databases are foundational to addressing business concerns. This implies an understanding of the techniques that may be used to model data and an ability to develop a concise conceptual model representing a given universe of discourse.

This assessment will involve creating a conceptual schema design for a given universe of discourse and the generation of a relational mapping.

You will use your knowledge from the lectures together with the techniques practiced in the tutorial sessions and apply both to a set of tasks that refer to some business concern. You will not only perform the necessary steps to solve the tasks but also provide an explanation of your approach.

Learning Outcome 1: Knowledge of conceptual data modelling principles to understand, capture and analyse an abstraction of the complex data aspect of business domains.
Learning Outcome 2: Develop rigorous database design underpinned by a conceptual data model

Instructions

This assessment is individual. You will have to solve two tasks:
(1) Apply the Conceptual Schema Design Process (steps 1 - 6) to a given business concern;
(2) Map a conceptual schema design to a relational database schema.

For task 1, you can use any modelling tool (including MS PowerPoint, LucidChart, Draw.io or even handwriting if it is understandable and clear) to draw your models. Make sure the diagrams are readable

Assessment Tasks

Task 1 - Individual

Congratulations on your recent appointment as database designer for the IT Department of Queensland University of Technology (QUT). Your first task is to develop a Staff Records database, starting with the design phase, using ORM. QUT IT requires this system to manage their growing team, system access and leave balances.

The requirements are as follows.

All staff demographic details must be recorded in the system. This includes their first name, surname, date of birth, mailing address, phone number, email, and gender (from a list: female (F), male (M), or other (O)). Part of your job is to perform advanced data analytics. When doing your database, be mindful that it is required to be able to efficiently access information including the suburbs where staff members live.

Currently, QUT offers the following jobs to staff members: IT Support Officer, IT Support Engineer, Head of IT, Junior Database Designer, Database Administrator, and Software Engineer. Each of these jobs are represented by a unique code, their description, and they are specific to a department at QUT. Each job is associated to a Pay Rate that can only range from Level 1 to 3, where Level 3 is the highest pay rate and Level 1 the lowest. The following table represents an example of the current information about QUT's staff members and their jobs.

Depending on the type of job, staff members will have access to different numbers of days for leave. There are only three types of leave that a staff member can take: Annual Leave, Personal Leave, and Emergency Leave. The leave days allocated to each staff member differ from each job. This means that a person that is a Junior Database Designer will have a different number of leave days from the Head of the IT department. The following table shows an example of the number of leave days a staff member can take.

Lastly, for security reasons, the system must store information on what staff member has access to each system. The information recorded must include the system (either O365, Google Drive, DropBox, or CRM) and the level of access (either read (R), edit (W) or admin access (A)). All staff have at least read access to all systems.

Assignment task:

Perform CSDP step 1 ~ 6 and present your final ORM diagram. You are not required to submit your results from each individual step. If you want to submit your elementary facts or note any assumptions in your diagram, you are free to do so, but only the final ORM diagram will be evaluated.

Task 2 - Individual
The following is an ORM model for employee. Map the schema to a relational database schema. Any possible constraints need to be included (e.g., primary key, foreign key).

Attachment:- Database Management.rar