Identified Entities And Their Attributes Information Technology Essay

Identified Entities And Their Attri scarcelyes In playation Technology Essay ripe voyage has inaugurated a modern travelling business surrounded by the airport and the bem utilize town. The travelling options they offer good deal 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 education pertaining to staff info, guest information, car or couch availability, node journey and payment details etc. An E-R sticker is developed establish on the above case study and it is dominionised into 3NF. A relative database ground on the normalised E-R forge is produced which demonstrate that your database stores the required data and relationship between distinct entities.Table of ContentsTable of gradesER Model conceptual model and legitimate modelsEntity relationship model is a database modelling regularity utilize to represent the conceptual data. conceptual data model is d isposed(p) when an information system is based on a database, which at the later stage is mapped to logical data model such(prenominal) as relational model which is in turn mapped to the physical model.Identified Entities and their AttributesAn entity has a manifest existence which is uniquely identified. An entity flock be a house, employee or a car. Entities argon described by a garment of properties c bothed delegates. Each entity essential have the protects for its attributes which is uniquely identifiable from other entities in a particular entity dictated. Following are the entities and its attributes for the latterly activate (LT). under are the Entities ( node, employee and involution) and there attributes.Attributes of an employee are shown in the figure to a lower place. here(predicate) Employee_id is a original key which is apply to uniquely identify each employee.EmployeeEmployee_idEmployee_address_idEmployee_nameEmployee_phoneOther_employee_details aim Entity (Employee) and its attributesAttributes of the customers are shown in the figure below. Here Customer_id is a direct 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 schedule is shown in the figure. Here interlocking_id is a essential key which is used to uniquely identify each troth by the customer. Customer_id is a contrary key which is mapped to Entity (customer) and outcome_code forget provide information whether the participation is spatecelled or not. Status_code field give give information whether status is confirm or waiting, for the customer for particular date. bookingbooking_idcustomer_idOutcome_codeStatus_codedate_of_bookingCar_idJourney_codeBooking_detailsFigure Entity (booking) and its attributesIdentified Relationships between EntitiesA relationship is an association among the e ntities. Relational database model represents the scar of relationship of alike type. Figure 4 shows the crop relationship between a customer and booking.Customercustomer_idpayment_idcustomer_nameCustomer_addressSexcustomer_phoneDate_of_birthDate_became_customerBookingbooking_idcustomer_idOutcome_codeStatus_codedate_of_bookingCar_idJourney_codeBooking_details come inFigure 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(prenominal) research comes which is whether a customer can order more(prenominal) than one booking or whether booking can be ordered by more than one customer. The question raised is that of cardinality. In fact the exercise above depicts that customer can order zero or more booking and likewise, a booking whitethorn 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. under figure depicts how arrows are used to represent cardinality mavenManyOneOneManyManyFigure CardinalityIn 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. in that locationfore 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 delineate by connecting the entity set to the relationship set with a double line. Figure 6 shows the relationship of an agent to a latish Travel surgical procedure 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 diligent by only Late Travel agency.Late Travel agencyEmploysAgentFigure Cardinality and participation constra intConceptual modelA conceptual model is a modelling of concepts and relationship. This term is used from a long magazine in a database design. It is independent of DBMS and allows docile communication between end users and the developers. Below is the conceptual model of Late Travel covering the entities, attributes and there relationship.Customercustomer_idpayment_idCustomer_nameCustomer_phoneCustomer_addressDate_of_birthSexDate_bacame_customerBookingbooking_idcustomer_idcar_idoutcome_codestatus_codejourney_codedate_of_bookingbooking_detail honorariumPayment_idbooking_idpayment_amountpayment_dateother_detailsRef_booking_statusStatus_codeStatus_description (eg sustain/waiting)Ref_booking_outcomeoutcome_codeoutcome_description (eg cancelled/ok)Car_detailsCar_id driver_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 arrangement of the objects in the system. However it is necessary to create an association among different entities and bring their multiplicity.Logical modelLogical model represents the abstract structure of information. It is used to display the business process that disciplineks 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 change magnitude 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 itinerary 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. discover 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. publicized E-R Model to 3NF familiarization is the way of ensuring that a relational database structure is free from insertion, update and deletio n anomalies that talent 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 gameboards 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 Nor mal Form (1NF)A relational display board X is in 1NF (First Normal form), if it meets certain set of criteria. This criterion ensures that each attribute is single-valued with nuclear values. In other words get across is free from iterate 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_details1100 illusion98787787, 8898987988none2 one hundre d oneSteve89898987,989898983102Mark98989898898Here, column employee_phone doesnt contains the atomic value therefore in order to take aim it 1NF separate entity such as customer_phone_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. trice 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 blurb 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 tableEmployee_idEmployee_add_idEmployee_nameEmployee_phoneOther_emp_details1100John98787787, 8898987988none2101Steve89898987,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 all(prenominal) non prime attri bute 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_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 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 tablesJourney_detailsJourney_code (PK)Customer_id (FK)Place_id (FK)Other_detailsPlace_detailsPlace_id (PK)Place_nameRenting_rateFigure Journey and place tableNow t he 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 QueriesBelow are the different SQL queries which will be generally used during the management of Late Travel database. Query result is displayed and explained belowSelect customer_name, customer_address, date_of_birth, conjure up, date_became_active 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 inclination of new customers added from a particular date.Output judge Output iscustomer_namecustomer_addressdate_of_birthsexdate_became_activeJohn35, paris road1988-09-06male2010-02-02Smith78, dahiya bhavan1978-07-08male2010-03-04David109, verdure park1980-08-08male2010-01-09Select emp.employee_id ID, emp.employee_name Name, addr.address mouth 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 disceptation of all employees. It might possible that at any instance of time it is required to get the information close to the employees working in Late Travel.Output Expected Output isIDNameAddress1John32 James park2Mark45 verdure 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 somewhat 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.customer_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 data 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 major ly 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 habituationKeyDomain 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. findingA 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.