Database Management Systems

Assessment is an individual report. This assignment will assess your ability to design and develop a small-scale database system. Your database should, if possible be a real one. Alternatively, you may create a simulated real world database based on information requirements in an organization with which you are familiar.

Individual Report

You may choose any one topic based on the organizations given below. You should get approval from the Lecturer before starting the assignment

- Hospitality Industry - Restaurant, Hotel, Club
- Health and medical Industry - Hospital, Clinic, Pharmacy, Spa, Gymnasium
- Government Agency (Ministry)
- Tour and Travel - Airline Ticket Booking, Bus Ticket, car Rental, Rail Reservation, Tour Planner.
- Academics and Education - School, College, University
- Retail - Inventory system and retail shop
- Finance - Insurance, Banking

The Report should include the following sections:

1. Initial Study

2. Requirements analysis.

3. Design

3.1 Modeling the requirement using the E/R conceptual model

3.2 Data Dictionary

3.3 Converting the conceptual model to logical schema

3.4 Normalizing the logical schema

Each of these sections is described in more detail below.

1. Initial Study
The goal of this phase is essentially to gather the company details and its business processes. The company details should be gathered in terms of name, location, when was it started, what is their main business? The details of business and/or operational processes used by the organization should be provided. Details on what the company wishes to accomplish, what problems and limitations users have, what is the new database system have to do, and in what way must it be done should also be given. The initial study should also include scope of the database.

2. Requirements gathering & analysis
In the requirements gathering & analysis phase, the requirements collected and analyzed. The collected requirements help to understand the system that does not yet exist. The purpose of the requirement analysis is to identify and describe the data that are used by the processes of the organization.

In this section you should use and document different methods like interview, questionnaire, observation, reading company documents and reports. The purpose of these methods is to gather all the information needs of users involved in or affected by the business process. The documentation for the interview should include the details of the interviewee, the questions asked, and the response. The documentation for the questionnaire should include the analysis of questions which make use of some data. Documentation for the observation and reading documents should include data used by the processes.

At the end required entities should be documented and relationship matrix should be given to depict the relationship between entities.

3. Design
In this phase, the information models that were developed during the analysis are used to design a conceptual schema for the database. In conceptual schema design, the data requirements collected in requirement gathering & analysis phase are examined and a conceptual database schema is produced. The conceptual design is then translated into the logical design and normalized.

3.1 Modeling the requirement using the E/R conceptual model
A data modeling phase involves modeling the database structure itself by using a method such as entity relationship diagramming (ER diagram). This provides a means of visually representing various aspects of the database structure, such as the tables, table relationships and relationship characteristics.

In this section you need to determine entities about which information is to be maintained, determine the properties or characteristics of those entities, determine the relationships between entities. Draw the entity relationship diagram using the Crow's Foot notation. You should draw the E-R diagram in Visio 2013. You should include all attributes for each entity and the relationship with cardinality and all keys must be properly documented. Your E-R diagram should have minimum of five to six entities.

3.2 Data Dictionary
In this section you need to produce the data dictionary for each Entity. The data dictionary should include name of the attribute, type, size, domain, description. The data dictionary should also include information on keys and constraints such as whether the specified attribute is a primary key, foreign key, unique, not null and whether there is any condition to check using the check constraint.

3.3 Converting the conceptual model to logical schema
In this section you should convert your E-R diagram to logical/relational schema. Use the proper rules to transform 1:1, 1: M/M: 1 and M: N to logical schema. You should underline the primary keys and put an asterisk for foreign keys.

3.4 Normalizing the logical schema
The Normalization phase is the process of decomposing large tables into smaller tables in order to eliminate redundant data and avoid problems with inserting, modifying or deleting data. Table structures are tested against normal forms, which are a specific set of rules that can be used to test a table structure to be sure it is sound and free of problems.

You need to check your produced Relational Schema above for 1NF, 2NF and 3NF.If it satisfies 1NF/2NF/3NF criteria then you ONLY need to include the statement "The Relational Schema satisfies 1NF/2NF/3NF criteria". If your schema does not satisfy the criteria of 1NF/2NF/3NF then you need to reproduce your schema in 1NF/2NF/3NF.The conversion should be documented.