Identified Entities And Their Attributes Information Technology Essay

Identified Entities And Their Attributes Information Technology Essay Late Travel has inaugurated a new travelling business between the airport and the lost town. The travelling options they offer can either their own chauffeured car or a coach service. A database is designed to assist the administration of this new service. The database contains the information pertaining to staff information, customer information, car or couch availability, customer journey and payment details etc. An E-R model is developed based on the above case study and it is normalised into 3NF. A relational database based on the normalised E-R model is produced which demonstrate that your database stores the required data and relationship between different entities. Table of Contents Table of Figures ER Model: conceptual model and logical models Entity relationship model is a database modelling method used to represent the conceptual data. Conceptual data model is prepared when an information system is based on a database, which at the later stage is mapped to logical data model such as relational model which is in turn mapped to the physical model. Identified Entities and their Attributes An entity has a distinct existence which is uniquely identified. An entity can be a house, employee or a car. Entities are described by a set of properties called attributes. Each entity must have the values for its attributes which is uniquely identifiable from other entities in a particular entity set. Following are the entities and its attributes for the Late Travel (LT). Below are the Entities (customer, employee and booking) and there attributes. Attributes of an employee are shown in the figure below. Here Employee_id is a primary key which is used to uniquely identify each employee. Employee Employee_id Employee_address_id Employee_name Employee_phone Other_employee_details Figure : Entity (Employee) and its attributes Attributes of the customers are shown in the figure below. Here Customer_id is a primary key which is used to uniquely identify each customer. Customer customer_id payment_id customer_name Customer_address Sex customer_phone Date_of_birth Date_became_customer Figure : Entity (Customer) and its attributes Attributes of a booking is shown in the figure. Here booking_id is a primary key which is used to uniquely identify each booking by the customer. Customer_id is a foreign key which is mapped to Entity (customer) and outcome_code will provide information whether the booking is cancelled or not. Status_code field will give information whether status is confirmed or waiting, for the customer for particular booking. Booking booking_id customer_id Outcome_code Status_code date_of_booking Car_id Journey_code Booking_details Figure : Entity (booking) and its attributes Identified Relationships between Entities A relationship is an association among the entities. Relational database model represents the set of relationship of same type. Figure 4 shows the order relationship between a customer and booking. Customer customer_id payment_id customer_name Customer_address Sex customer_phone Date_of_birth Date_became_customer Booking booking_id customer_id Outcome_code Status_code date_of_booking Car_id Journey_code Booking_details order Figure : Relationship set of customer and booking Figure 4 represents the idea that a customer orders a booking for hiring a car or equivalently, a booking is ordered by the customer. Here the another question comes which is whether a customer can order more than one booking or whether booking can be ordered by more than one customer. The question raised is that of cardinality. In fact the example above depicts that customer can order zero or more booking and likewise, a booking may be ordered by zero or more customer. An arrow is used to connect the entities when one-to-one or one-to-many relationship is modelled. Below figure depicts how arrows are used to represent cardinality: One Many One One Many Many Figure : Cardinality In the previous example it is reasonable to expect that a customer can exist without having order a single booking, and conversely, that booking exist even though none have been ordered yet. Therefore none of the entities in an entity set may participate in a particular relationship is partial participation. In contrast to this, an entity set may participation in a relationship. Diagrammatically, this is represented by connecting the entity set to the relationship set with a double line. Figure 6 shows the relationship of an agent to a Late Travel agency which combines both cardinality and participation. It is clearly stated that Late Travel agency can employs zero or more agents, and that an agent must be employed by only Late Travel agency. Late Travel agency Employs Agent Figure : Cardinality and participation constraint Conceptual model A conceptual model is a modelling of concepts and relationship. This term is used from a long time in a database design. It is independent of DBMS and allows easy communication between end users and the developers. Below is the conceptual model of Late Travel showing the entities, attributes and there relationship. Customer customer_id payment_id Customer_name Customer_phone Customer_address Date_of_birth Sex Date_bacame_customer Booking booking_id customer_id car_id outcome_code status_code journey_code date_of_booking booking_detail Payment Payment_id booking_id payment_amount payment_date other_details Ref_booking_status Status_code Status_description (eg: confirmed/waiting) Ref_booking_outcome outcome_code outcome_description (eg: cancelled/ok) Car_details Car_id Driver_id Seats_avail Car_no Driver_details Driver_id Driver_name Driver_address Driver_phone Journey_details Journey_code Customer_id Place_name Renting_rate Other_details Order Goes for Makes has consist of has Consist of Figure : Conceptual model Above figure shows the conceptual model of Late Travel. The primary objective of the conceptual model is to establish a base understanding of the objects in the system. However it is necessary to create an association among different entities and select their multiplicity. Logical model Logical model represents the abstract structure of information. It is used to display the business process that seeks to capture the importance things usually in the form of a diagram. Some of the benefit of using the logical model is to improve the business process, facilitates reuse of data, gather functional information and decrease the system design cost and time. Figure 7 is already logical as it shows the attributes and relationship among the different entities. All attributes are appearing in a data store. Above diagram is showing the business entities, their inter-relationships, and the cardinality i.e. each customer can order one or more booking of a car for their journey and same way a booking can be ordered by one or more customers-these relationships can be viewed from both ends. Is database is storing the required data? In order to identify whether the database is storing the necessary data, following are the tasks that are needed to be performed in an iterative manner. Identify the entity type. Identify the attributes of the entity. Application of naming convention. Identify relationship among entities. Data model patterns need to be applied. Assign keys such as primary, unique and foreign key. To reduce data redundancy it should be normalized. All these tasks are being performed on this case study Late Travel (LT). Therefore required data is being stored in the database. And as it is already stated that above tasks are needed to be performed in an iterative manner, therefore it is a continuous process. Normalized E-R Model to 3NF Normalization is the way of ensuring that a relational database structure is free from insertion, update and deletion anomalies that might lead to loss of integrity of data. In 1970s E.F. Codd and his team at IBM introduced the concept of relational databases, proposed normalization through several normal forms. Below tables are assigned with the keys. Customer customer_id (PK) payment_id (FK) Customer_name Customer_phone Customer_address Date_of_birth Sex Date_bacame_customer Booking booking_id (PK) customer_id (FK) car_id (FK) outcome_code (FK) status_code (FK) journey_code (FK) date_of_booking booking_detail Payment Payment_id (PK) booking_id (FK) payment_amount payment_date other_details Ref_booking_status Status_code (PK) Status_description (eg: confirmed/waiting) Ref_booking_outcome outcome_code (PK) outcome_description (eg: cancelled/ok) Car_details Car_id (PK) Driver_id (FK) Seats_avail Car_no Driver_details Driver_id (PK) Driver_name Driver_address Driver_phone Journey_details Journey_code (PK) Customer_id (FK) Place_name Renting_rate Other_details Order Goes for Makes has consist of has Consist of Figure : Tables of Late Travel First Normal Form (1NF) A relational table X is in 1NF (First Normal form), if it meets certain set of criteria. This criterion ensures that each attribute is single-valued with atomic values. In other words table is free from repeating groups. Now if we check whether the logical model discussed above is in 1NF or not. Lets discuss the employee and customer table. Employee employee_id (PK) employee_address_id (FK) employee_name employee_phone other_employee_details Customer customer_id (PK) payment_id (FK) Customer_name Customer_phone Customer_address Date_of_birth Sex Date_became_customer Driver_details Driver_id (PK) Driver_name Driver_address Driver_phone Figure : Employee and customer table As 1NF says that there should be no repeating group but in this case phone number of employee or customer can be more than one. Therefore this relational table is not in first normal form e.g. Employee_id Employee_add_id Employee_name Employee_phone Other_emp_details 1 100 John 98787787, 8898987988 none 2 101 Steve 89898987,98989898 3 102 Mark 98989898898 Here, column employee_phone doesnt contains the atomic value therefore in order to make it 1NF separate entity such as customer_phone_num and employee_phone_num is needed to be defined. Employee employee_id (PK) employee_address_id (FK) employee_name other_employee_details Customer customer_id (PK) payment_id (FK) Customer_name Customer_address Date_of_birth Sex Date_became_customer Employee_phone employee_id (PK) phone_num Customer_phone customer_id (PK) phone_num Driver_details Driver_id (PK) Driver_name Driver_address Driver_phone Driver_phone driver_id (PK) phone_num Figure : Employee, customer and driver table Now all the tables are in First Normal Form as all the columns are having the atomic values. Second Normal Form (2NF) A relational table X is in Second Normal Form when table is in 1NF. A table that is in 1NF must meet the criteria to qualify for the second normal form. This criterion ensures that set of attributes X is functionally dependent on set of attributes Y. For instance, lets look at the booking table: Employee_id Employee_add_id Employee_name Employee_phone Other_emp_details 1 100 John 98787787, 8898987988 none 2 101 Steve 89898987,98989898 3 102 Mark 98989898898 Booking booking_id (PK) customer_id (FK) car_id (FK) outcome_code (FK) status_code (FK) journey_code (FK) date_of_booking booking_detail Figure : Booking table Booking_id Customer_id Car_id Outcome_code Status_code Journey_code Date_of_booking Booking_details 1 1001 100 1 3 2 24-Mar-10 2 1002 101 2 4 3 25-Mar-10 Here as we can see that, none of its non prime attributes are functionally dependent on a subset of a candidate key. Therefore the tables are already in second normal form. Following are the tables that are designed to store the information regarding customer, employees, car, journey and booking. These tables meet the criteria to be second normal form. Hence there is no need to alter the tables. Third Normal Form (3NF) A relation table X is in second normal form when every non prime attribute R is non-transitively dependent on every key of R. In other words a relationship database is in 3NF if it is in 2NF and no non-primary attribute is functionally dependent on another non primary key. Lets look at one of the table journey_details of Late Travel. Journey_details Journey_code (PK) Customer_id (FK) Place_name Renting_rate Other_details Figure : Journey table Journey_code Customer_id Place_name Renting_rate Other_details 001 331 Airport 500 002 332 Airport 500 Here as we can see that renting rate depends upon the place, therefore this table is not in third normal form. In order to make it in third normal form two separate tables or entities would be created- One containing the journey code, customer_id, place_id and other_details and the other containing the place_id, place_name and renting_rate. Below figure is showing the two tables: Journey_details Journey_code (PK) Customer_id (FK) Place_id (FK) Other_details Place_details Place_id (PK) Place_name Renting_rate Figure : Journey and place table Now the E-R model is normalized into third normal form (3NF). Now all the tables contain the atomic values and non primary attributes are functionally dependent on primary key. And lastly no non-primary key attribute is functionally dependent on another non-primary key. SQL Queries Below are the different SQL queries which will be mostly used during the management of Late Travel database. Query result is displayed and explained below: Select customer_name, customer_address, date_of_birth, sex, date_became_active from Customer WHERE Date_became_customer > 2010-01-01 Description: This query will display all the active customers since 1st Jan 2010. Why Needed: This query is important because it displays the list of new customers added from a particular date. Output: Expected Output is: customer_name customer_address date_of_birth sex date_became_active John 35, paris road 1988-09-06 male 2010-02-02 Smith 78, dahiya bhavan 1978-07-08 male 2010-03-04 David 109, green park 1980-08-08 male 2010-01-09 Select emp.employee_id ID, emp.employee_name Name, addr.address Address from Employee emp, emp_Address addr WHERE emp.employee_id = addr.employee_id Description: This query will display all the employees with their details. Why Needed: This query is important because it displays the list of all employees. It might possible that at any instance of time it is required to get the information about the employees working in Late Travel. Output: Expected Output is: ID Name Address 1 John 32 James park 2 Mark 45 green avenue 3 Steve 14 Vikas puri Select car.car_id ID, car.car_no Car No., driver.driver_name Driver Name, driver.driver_address Driver Address, FROM Car_details car, Driver_details driver WHERE car.driver_id = driver.driver_id Description: This query will display the information about the particular car like who is the driver, car number etc. Why Needed: This query is important because it is required to get the information about the particular car and the driver who is responsible of handling it. Output: Expected Output is: ID Car No Driver Name Driver Address 1 HGH 7678 Austin 23, Qutub Enclave 2 HKK 101 Preety 44, Hilton 3 NMN 420 Lisa 88, oberios Select * FROM Booking RIGHT JOIN Journey On Booking.customer_id =Journey.customer_id WHERE date_of_booking = 2010-03-24 Description: This query will display all the booking for 24 Mar 2010 with details of journey. Why Needed: This query is the basic one as it shows the booking for a particular date. It will display all the information related to booking for whether car is available or not or booking is confirmed or pending. Output: Expected Output is: Booking_id Customer_id Car_id Outcome_code Status_code Journey_code Date_of_booking Booking_details Journey_code Place_name Other_details 1 1001 100 1 3 2 24-Mar-10 001 Airport 2 1002 101 2 4 3 24-Mar-10 002 Airport Critical Reflection Whether we use a file or relational database, depends on the nature of the data and how we conceptualize it. There are some major problems and issues arise during the development of the database majorly of data integrity, performance and flexibility. Normally many of the people result into development of database which contains redundant data or insertion, deletion and modification anomalies. In order to understand the normal form, four terms that must be understood are: Dependency Key Domain and Restriction Designing of database plays an important role in integrity and reliability of the data, therefore it is necessary to carefully inspect the database structure, entities and their relationship. Conclusion A database is designed for Late Travel (LT) which is used to store the information related to employee, customer, journey, payment, car and driver details. A conceptual and E-R diagram is developed based on the above case study, showing the relationship among different entities. All the tables are normalized into the third normal form and then the set of queries are fired to show the retrieval of information from database.