Identified Entities And Their Attributes Information Technology Essay

Identified Entities And Their Attributes In influenceation Technology EssayLate Travel has inaugurated a new travelling business mingled with the airport and the lost town. The travelling options they offer end either their own chauffeured car or a coach service. A schoolingbase is designed to assist the brass of this new service. The infobase contains the learning pertaining to staff information, customer information, car or couch availability, customer journey and payment details etc. An E-R model is developed base on the above case study and it is conventionised into 3NF. A relational database based on the normalised E-R model is produced which demonstrate that your database repositions the required data and relationship between contrastive entities.Table of ContentsTable of FiguresER Model conceptual model and sensible modelsEntity relationship model is a database modelling method employ to exhibit the conceptual data. Conceptual data model is prepared when an info rmation 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 AttributesAn entity has a distinct existence which is unequivoc whollyy identified. An entity can be a ho intake, 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 extra entity set. Following are the entities and its attributes for the Late Travel (LT). Below are the Entities (customer, employee and arriere pen enamour) and there attributes.Attributes of an employee are shown in the embark below. Here Employee_id is a primary attain which is used to uniquely identify each employee.EmployeeEmployee_idEmployee_address_idEmployee_nameEmployee_phoneOther_employee_detailsFigure Entity (Employee) and its attributesAttributes of the customers are show n in the figure below. Here Customer_id is a primary key which is used to uniquely identify each customer.Customercustomer_idpayment_idcustomer_nameCustomer_addressSexcustomer_phoneDate_of_birthDate_became_customerFigure Entity (Customer) and its attributesAttributes 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 lead provide information whether the booking is scratch or not. Status_code field will give information whether status is confirmed or waiting, for the customer for particular booking.Bookingbooking_idcustomer_idOutcome_codeStatus_codedate_of_bookingCar_idJourney_codeBooking_detailsFigure Entity (booking) and its attributesIdentified Relationships between EntitiesA relationship is an association among the entities. comparative database model represents the set of relationship of same type. Figure 4 sh ows the baffle relationship between a customer and booking.Customercustomer_idpayment_idcustomer_nameCustomer_addressSexcustomer_phoneDate_of_birthDate_became_customerBookingbooking_idcustomer_idOutcome_codeStatus_codedate_of_bookingCar_idJourney_codeBooking_detailsorderFigure Relationship set of customer and bookingFigure 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 cardinalityOne umteenOneOneManyManyFigure Cardin alityIn 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 function which combines both cardinality and participation. It is clearly stated that Late Travel representation can employs zero or more agents, and that an agent must be employed by only Late Travel agency.Late Travel agencyEmploysAgentFigure Cardinality and participation constraintConceptual modelA conceptual model is a modelling of concepts and relationship. This term is used from a long time in a database design. It is in int erdependent of DBMS and allows easy communication between end users and the developers. Below is the conceptual model of Late Travel presentation the entities, attributes and there relationship.Customercustomer_idpayment_idCustomer_nameCustomer_phoneCustomer_addressDate_of_birthSexDate_bacame_customerBookingbooking_idcustomer_idcar_idoutcome_codestatus_codejourney_codedate_of_bookingbooking_detailPaymentPayment_idbooking_idpayment_amountpayment_dateother_detailsRef_booking_statusStatus_codeStatus_description (eg confirmed/waiting)Ref_booking_outcomeoutcome_codeoutcome_description (eg cancelled/ok)Car_detailsCar_idDriver_idSeats_availCar_noDriver_detailsDriver_idDriver_nameDriver_addressDriver_phoneJourney_detailsJourney_codeCustomer_idPlace_nameRenting_rateOther_detailsOrderGoes forMakeshasconsist ofhasConsist ofFigure Conceptual modelAbove figure shows the conceptual model of Late Travel. The primary objective of the conceptual model is to establish a base understanding of the ob jects in the system. However it is necessary to take a crap an association among different entities and select their multiplicity.Logical modelLogical model represents the abstract structure of information. It is used to pomp the business process that seeks to capture the impressiveness 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 orde r to identify whether the database is storing the necessary data, following are the tasks that are needed to be performed in an iterative manner. localise 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 suppress data redundancy it should be normalized.All these tasks are be 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. conventionalityized E-R Model to 3NF standardisation is the way of ensuring that a relational database structure is free from insertion, update and deletion anomalies that might lead to loss of truth of data. In 1970s E.F. Codd and his team at IBM introduced the concept of relational databases, proposed normal ization through several normal forms.Below control panels are assigned with the keys.Customercustomer_id (PK)payment_id (FK)Customer_nameCustomer_phoneCustomer_addressDate_of_birthSexDate_bacame_customerBookingbooking_id (PK)customer_id (FK)car_id (FK)outcome_code (FK)status_code (FK)journey_code (FK)date_of_bookingbooking_detailPaymentPayment_id (PK)booking_id (FK)payment_amountpayment_dateother_detailsRef_booking_statusStatus_code (PK)Status_description (eg confirmed/waiting)Ref_booking_outcomeoutcome_code (PK)outcome_description (eg cancelled/ok)Car_detailsCar_id (PK)Driver_id (FK)Seats_availCar_noDriver_detailsDriver_id (PK)Driver_nameDriver_addressDriver_phoneJourney_detailsJourney_code (PK)Customer_id (FK)Place_nameRenting_rateOther_detailsOrderGoes forMakeshasconsist ofhasConsist ofFigure Tables of Late TravelFirst Normal pass water (1NF)A relational flurry X is in 1NF (First Normal form), if it gibes 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.Employeeemployee_id (PK)employee_address_id (FK)employee_nameemployee_phoneother_employee_detailsCustomercustomer_id (PK)payment_id (FK)Customer_nameCustomer_phoneCustomer_addressDate_of_birthSexDate_became_customerDriver_detailsDriver_id (PK)Driver_nameDriver_addressDriver_phoneFigure Employee and customer tableAs 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_idEmployee_add_idEmployee_nameEmployee_phoneOther_emp_details1100John98787787, 8898987988none2101Steve89898987,989898983102 smirch98989898898Here, column employee_phone doesnt contains the atomic value therefore in order to make it 1NF separate entity such as customer_pho ne_num and employee_phone_num is needed to be defined.Employeeemployee_id (PK)employee_address_id (FK)employee_nameother_employee_detailsCustomercustomer_id (PK)payment_id (FK)Customer_nameCustomer_addressDate_of_birthSexDate_became_customerEmployee_phoneemployee_id (PK)phone_numCustomer_phonecustomer_id (PK)phone_numDriver_detailsDriver_id (PK)Driver_nameDriver_addressDriver_phoneDriver_phone number one wood_id (PK)phone_numFigure Employee, customer and driver tableNow 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 see at the booking tableEmployee_idEmployee_add_idEmployee_nameEmployee_phoneOther_emp_details1100John98787787, 8898987988none2101Steve8989 8987,989898983102Mark98989898898Bookingbooking_id (PK)customer_id (FK)car_id (FK)outcome_code (FK)status_code (FK)journey_code (FK)date_of_bookingbooking_detailFigure Booking tableBooking_idCustomer_idCar_idOutcome_codeStatus_codeJourney_codeDate_of_bookingBooking_details1100110013224-Mar-102100210124325-Mar-10Here 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 pr imary key.Lets look at one of the table journey_details of Late Travel.Journey_detailsJourney_code (PK)Customer_id (FK)Place_nameRenting_rateOther_detailsFigure Journey tableJourney_codeCustomer_idPlace_nameRenting_rateOther_details001331Airport500002332Airport500Here as we can see that renting rate depends upon the place, therefore this table is not in deuce-ace 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 tablesJourney_detailsJourney_code (PK)Customer_id (FK)Place_id (FK)Other_detailsPlace_detailsPlace_id (PK)Place_nameRenting_rateFigure Journey and place tableNow 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 no n-primary key attribute is functionally dependent on another non-primary key.SQL QueriesBelow are the different SQL queries which will be in general used during the management of Late Travel database. Query result is displayed and explained belowSelect customer_name, customer_address, date_of_birth, sex, date_became_ restless from CustomerWHERE Date_became_customer 2010-01-01Description 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 signal Expected Output iscustomer_namecustomer_addressdate_of_birthsexdate_became_activeJohn35, capital of France road1988-09-06male2010-02-02Smith78, dahiya bhavan1978-07-08male2010-03-04David109, green park1980-08-08male2010-01-09Select emp.employee_id ID, emp.employee_name Name, addr.address Address from Employee emp, emp_Address addrWHERE emp.employee_id = addr.employee_idDescription 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 active the employees working in Late Travel.Output Expected Output isIDNameAddress1John32 James park2Mark45 green avenue3Steve14 Vikas puriSelect 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 driverWHERE car.driver_id = driver.driver_idDescription 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 isIDCar NoDriver NameDriver Address1HGH 7678Austin23, Qutub Enclave2HKK 101Preety44, Hilton3NMN 420Lisa88, oberiosSelect * FROM BookingRIGHT JOIN JourneyOn Booking.custo mer_id =Journey.customer_idWHERE date_of_booking = 2010-03-24Description 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 isBooking_idCustomer_idCar_idOutcome_codeStatus_codeJourney_codeDate_of_bookingBooking_detailsJourney_codePlace_nameOther_details1100110013224-Mar-10001Airport2100210124324-Mar-10002AirportCritical ReflectionWhether 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 ord er to understand the normal form, four terms that must be understood areDependencyKeyDomain andRestrictionDesigning 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.ConclusionA 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.