Reference no: EM132265863

Database Management Systems - Assessment

Individual Report

You may choose any one topic based on the organizations (may not be a real company) given below. You should get approval from the Lecturer before starting the assignment

• Hospitality Industry - Database for Restaurant or Hotel,
• Health and medical Industry - Database Hospital, Clinic, Pharmacy
• Government Agency - Database for Ministry Departments
• Tour and Travel - Database for Airline Ticket Booking, Bus Ticket, car Rental,
• Academics and Education - Database for College, University
• Retail - Database for Inventory system and retail shop
• Finance - Database Insurance Company or Banking

Each of these sections is described below in more details.

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 conceptual models and logical model has to be developed based on the requirement gathering & analysis. In conceptual schema design, the data requirements collected in requirement gathering & analysis phase are examined and a conceptual database schema is produced. The conceptual schema is then translated into the logical model and normalized.

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.

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. The data dictionary section must be supported with proper explanation and justification. The explanation must include criteria of considering PK and other constraints.

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.

Attachment:- Assignment Description.rar