XJTLU Entrepreneur College (Taicang) Cover Sheet

Module code and Title	DTS106TC: Introduction to Database
School Title	School of AI and Advanced Computing
Assignment Title	Resit Assessment Task R001 (CW)
Submission Deadline	26th July 2023 at 17:00
Final Word Count	N/A
If you agree to let the university use your work anonymously for teaching and learning purposes, please type “yes” here.

I certify that I have read and understood the University’s Policy for dealing with Plagiarism, Collusion and the Fabrication of Data (available on Learning Mall Online). With reference to this policy I certify that:

· My work does not contain any instances of plagiarism and/or collusion.
My work does not contain any fabricated data.

Students

(Please modify where necessary)

The assignment must be typed in an MS Word document and submitted as pdf via Learning Mall Online. Only electronic submission is accepted and no hard copy submission.

All students must download their file and check that it is viewable after submission. Documents may become corrupted during the uploading process (e.g. due to slow internet connections). However, students themselves are responsible for submitting a functional and correct file for assessments.

Please check the last part of this assessment to see the list of files you need to submit as part of this assessment.

Resit Assessment R001: Coursework

Due: 26^th July 2023 at 17:00

Weight: This Coursework (CW) counts for 100% of this module.

Maximum Marks: 100

The course work will be assessed for the following learning outcomes:

A. Demonstrate a basic understanding of the design of databases.

B. Show a fundamental grounding in the operation and usage of database management systems including "hands-on" experience of a basic database management system.

C. Demonstrate in-depth knowledge of the database language, SQL.

D. Show understanding of the legal processes and implications of creating and maintaining information systems.

Submission guidelines: You must write your report in MS word and submit it as pdf. Your report should be named as yourID_report.pdf. Your report should contain the following:

1. The first page of this document with your ID for grading purposes.

2. Section for each part of your coursework, named as follows:

a. Business requirements and justification.

b. Conceptual design.

c. Normalisation.

d. Relational model.

e. Physical design.

f. SQL queries.

g. Relational algebra queries.

Assessment Tasks:

Your task is to design and develop a database application on hotel reservation system. You are required to carefully read and understand the project scenario and work your way through the conceptual, relational and physical designs of the proposed database management system (DBMS). You will apply the knowledge you have gained, and skills your have developed, e.g. application development skills, to design, develop and deploy the proposed DBMS solution (both the front and backend). During the course of your activities, you also need to conduct a thorough research on the ethical issues and legal standards surrounding the problem area to which you must adhere to during your design work.

The skills required and further developed while carrying out the given task would be useful as a professional DBMS or IT manager. These individuals are usually responsible to capture business needs of the organisation they work for and are often tasked with developing solution/leading the development to organisation problems while keeping ethical, legal and financial considerations at the heart of there role or any activity surrounding it.

As stated above, there are 7 parts to your CW, the further elaboration of which is given in the following sections. The marking scheme of your CW is given below:

a. Business requirements and justification [10 marks].

b. Conceptual design using Entity-Relationship Diagram (ERD) [15 marks].

c. Normalisation [10 marks].

d. Relational model [15 marks].

e. Physical design that constitute constructing tables, inserting sample data [20 marks].

f. Construct SQL queries to run on the database [15 marks].

g. Construct relational algebra queries to run on the database [15 marks].

The report should also reflect upon your process, thoughts and assumptions during the design and development work. You should construct your database in APEX and produce a set of queries to run on the database as per this brief.

Assessment criteria

Please see the rubric at the end of this assessment document that outlines the assessment criteria.

Project Scenario

You are a database developer who runs a database design company. Your work as a consultant and lead to design, develop and deliver DBMSs for organisations based on their business needs.  You have been awarded a contract by a small chain of hotels to design and develop a database to manage its reservations. This includes information on the hotel, hotel branch, hotel reservees and guests, hotel rooms and their types, room bookings, room rates, etc. Some of the information for example required in the database may include:

Hotel information such as hotel id, type, name, URL, etc. (may vary)

Hotel branch information such as branch code, hotel id, hotel branch name, city, zipcode, URL, etc. (may vary)

Reservee/guest(s) information including their name, identification document, address, phone, email, etc. (may vary)

Hotel rooms information such as room numbers, room floor, room floor count, etc. (may vary)

Hotel rooms types information such as room type and its code, room description, room rates (standard, delux), restriction (e.g. smoking allowed), etc. (may vary)

Room booking information such as number of rooms booked, booking id, booking from date, booking to date, etc. (may vary)

Room rate information such as room type and its rate, room period description, rate period code, etc. (may vary)

The room booking system intends to provide detailed information on the guests/reservees, season bookings, information on the hotel branch, its rooms, room types and their rates and much more to conduct some reservation analysis. The database would be accessible in English only which could be further extended in the future.

Setup

You can complete this coursework either using Oracle Application Express (APEX) or SQLite. All you need is to submit SQL scripts for constructing and querying the database along with the report (see the end of the brief for the list of files required for submission).

Q1: Business requirements and justification [10 marks]

Based on the information provided above, you need to identify the business requirements to help you to understand the business processes involved in running this type of organisation. Build a list of business needs, rules, assumptions and ethical/regulatory considerations based on the scenario and some research of your own. Use the following information to help you with this part:

Business rule: Set of rules to understand business processes and the nature, role and scope of data. An example is provided.

· The hotel may book one or more rooms for the guest but at least one room must be booked by the hotel for the guest.

Assumption: Defined as a fact or statement necessary for such system but discrepancies might be possible.

Problem: A situation or scenario that requires attention in certain situations with possible solution, e.g. in case of multiple room bookings ensuring each room has a reservee/guest(s).

Q2: Conceptual design using ERD [15 marks]

With the help of the above scenario, identify any potential entities to represent in the relational database system. Entities represent nouns in the scenario description, but be mindful that not every noun could qualify as an entity in ER model. Additionally, identify potential attributes related to the entities, examples of which are given in the project scenario section. Remember that attributes can normally be found by identifying nouns describing other nouns (our entities). Identify both optional(o) and mandatory(*) attributes. Identify any potential candidate key(s) (attributes used to identify entities uniquely) and list them with ‘#’ sign as well as move it to the top of the list of attributes associated with the entity. This would result in you having entities and list of attributes (optional or mandatory) with associated candidates keys.

You are also required to identify and establish relationships between two or more entities you have identified above to represent the association. Using business rules given in Q1, identify potential associations between entities. Keeping in mind that the relationship exists in both directions, it must capture the optionality of a relationship between entities. An example might include: Hotel entity have one to many relationship with hotel booking entity. Therefore:

· The branch can book one or more rooms for the reservee/guest.

· At least one room must be booked by the hotel for the reserve/guest.

The overall aim of this task is to identify and create entities, establish their attributes and relationships between entities to build ERD to provide an overview of the overall system and how it is inter-linked.

Q3: Normalisation [10 marks]

The ERD would represent an un-normalised data. You need to apply the principles of the

First, Second and Third Normal Forms so that the data can be said to be atleast in 3NF. Note that some entities might be in some normal form already, if so, briefly explain them.

Definitions:

· The first normal form states that all attributes have a single value - no multivalued attributes.

For example, each booked room should have only one reservee, each room can be catagorised as one room type, and so on.

· The second normal form says that all attributes must be dependent on the whole (primary) key of  the entity. In other words, every column in a table must be functionally dependent on the whole primary key of that table. For example, if the room rate entity has one column primary key the attribute must depend on that key.

· The third normal form states that no non-UID attribute can be dependent on another non-UID

attribute. For example, a room floor count attribute cannot depend on room floor attribute.

In the normalisation process, you might create some new entities while resolving functional, transitive

and multivalued dependencies. Write down the final list of entities in relational notation. An example is given below for a representative hotel entity:

Hotel_branch (branch_code, hotel_id, branch_name, branch_street, branch_city, branch_zipcode, branch_URL) where branch_id is the primary key and hotel_id is the foreign key.

Q4: Relational model [15 marks]

You will take your normalised data that was created in the previous step to map them into relation. Use table instance chart to map ERD into a relational model. The table diagram helps you map out a table before creating it in the database. Your task is to also describe the design of each table, by completing the table instance chart for each table mapped. You can use Oracle SQL data modeler and ‘Engineer to Relational’ option to create relational model. The table instance chart will help you to verify the results matched with your table map. An example is given below for reference only:

Table name	Table short name
Customers	ctr
Key type	Optionality	Column name	Data type	Size
pk	*	h_id	VARCHAR2	10
uk	*	name	VARCHAR2	50
	*	address	VARCHAR2	20
	*	zipcode	VARCHAR2	12
fk1	o	code	VARCHAR2	4

Q5: Physical model [20 marks]

Create a physical database design based on both the conceptual and logical models you created in previous steps. Carry out the following steps to construct the physical database:

5a) Write the SQL DDL statements to create the fully normalised database. The SQL should contain

CREATE statements for each table. You should include indexes and foreign keys where appropriate, and

list and justify these in your report. Be sure to include appropriate constraints.

5b) Write INSERT statements to populate the new tables. Insert enough records (at least 10 rows in each

table) so that you can run interesting and nontrivial queries on your database.

Consider the order of populating tables. A table with a foreign key field cannot be populated before the related table with the primary key. The SQL statements to create tables and insert records should be saved as DDL.sql and data.sql and submitted along with your report.

Q6: SQL Querying [15 marks]

In this part, you will retrieve data from the database system you created using SELECT statements. This would allow your tutor to assess your SQL skills.

You should write at least 10 queries that utilises different SQL operators. Write retrieval statements in English, SQL code and provide the results of your queries as screenshots. Also, save your query code as SQLScript.sql and submit along your report.

Avoid listing very basic operations that only show tuples of relations. Challenging queries would help you to maximise your marks. It is advisable to use queries that demonstrate different SQL skills. The complexity of the query would be measured based on factors such as number of joins, select conditions, GROUP BY clauses. At the complexity level, a select condition count as ‘1’, the join count as ‘2’ and GROUP BY clause/condition count as ‘3’. E.g. a query with two select and one join will be classed at the complexity level ‘3’.

Q7: Relational algebra queries [15 marks]

You are required to write down five relational algebra queries that can be evaluated with your schema definitions. You can either write equivalent relational algebra queries as of Q6 or write different queries. In any case, you should state the English specification of the query, as well as state the query as relational algebra expression.

The complexity your query will be measured would constitute the number of joins, select conditions and aggregate function, where a select condition counts as a ‘1’, a join count as a ‘2’ and an aggregate function count as a ‘3’ (so, a relational algebra query that probably involved two select conditions, one join condition, and an aggregate function will be counted as the complexity of ‘4’).

Files

Your submission should be a single zip file and must include the following:

1. yourID_report.pdf.

2. DDL.sql: The DDL statements of the normalised database.

3. data.sql: INSERT statements of the normalised dataset.

4. sqlScrip.sql: SQL statements to run queries against normalised tables.