Entity Relationship Diagram for Normalisation

Question: Talk about the ERD for Normalization Functions. Answer: ERD: Standardization: First Normalization Form: Wiping out rehashing gatherings VIPStatus (VIPStatusID, VIPStatusName, DiscountPtge) Organization (CompanyID, CompanyName, Address, Phone, Fax, Email) Client (CustomerID, FirstName, LastName, Address, City, State, CompanyID, Mobile, PostCode, Email, CustomerTypeName, FFDiscount, VIPStatusID) Staff (StaffID, StaffName, Address, Phone, Email, SupervisorID, CompanyID) Area (LocationID, LocationName, State, City, PostCode) TravelBooking (TravelID, DepartureDate, ArrivalDate, OverallTravelAmount, CustomerID, StaffID, CompanyID, NumAdult, NumChild, InvoiceDate, TravelPackageAmount, IsAmountPaid, TravelPackageID, Description, PackageAmount, DatePkgIssued, DatePkgExpired, DepartingDate, ReturnDate, AirlineID, AirlineName, DepartureLocationID, DestinationLocationID, FlightID, FlightName, DateOfDeparture, DateOfArrival, CabinClass, AirlineCharges, HotelID, HotelName, LocationID, Phone) Second Normalization Form: Wiping out excess information VIPStatus (VIPStatusID, VIPStatusName, DiscountPtge) Organization (CompanyID, CompanyName, Address, Phone, Fax, Email) Client (CustomerID, FirstName, LastName, Address, City, State, CompanyID, Mobile, PostCode, Email, CustomerTypeName, FFDiscount, VIPStatusID) Staff (StaffID, StaffName, Address, Phone, Email, SupervisorID, CompanyID) Area (LocationID, LocationName, State, City, PostCode) Lodgings (HotelID, HotelName, LocationID, Phone) TravelPackage (TravelPackageID, Description, PackageAmount, DatePkgIssued, DatePkgExpired, DepartingDate, ReturnDate) Carrier (AirlineID, AirlineName, DepartureLocationID, DestinationLocationID, FlightID, FlightName, DateOfDeparture, DateOfArrival, CabinClass, AirlineCharges) TravelBooking (TravelID, DepartureDate, ArrivalDate, OverallTravelAmount, CustomerID, StaffID, CompanyID, NumAdult, NumChild, InvoiceDate, TravelPackageAmount, IsAmountPaid, TravelPackageID, AirlineID, HotelID) Third Normalization Form: Expel Transitive Dependency CustomerType (CustomerTypeID, CustomerTypeName) Organization (CompanyID, CompanyName, Address, Phone, Fax, Email) Client (CustomerID, FirstName, LastName, Address, City, State, CompanyID, Mobile, PostCode, Email, CustomerTypeID) Staff (StaffID, StaffName, Address, Phone, Email, SupervisorID, CompanyID) VIPStatus (VIPStatusID, VIPStatusName, DiscountPtge) Celebrity (CustomerID, VIPStatusID) FrequentFlyer (CustomerID, FFDiscount) Area (LocationID, LocationName, State, City, PostCode) Flight (FlightID, FlightName) Lodgings (HotelID, HotelName, LocationID, Phone) TravelPackage (TravelPackageID, Description, PackageAmount, DatePkgIssued, DatePkgExpired, DepartingDate, ReturnDate) Carrier (AirlineID, AirlineName, DepartureLocationID, DestinationLocationID, FlightID, DateOfDeparture, DateOfArrival, CabinClass, AirlineCharges) TravelBooking (TravelID, DepartureDate, ArrivalDate, OverallTravelAmount, CustomerID, StaffID, CompanyID, NumAdult, NumChild) TravellingDetails (TravelPackageID, AirlineID, TravelID) StayAt (HotelID, TravelID, TravelPackageID) Receipt (InvoiceID, InvoiceDate, TravelPackageAmount, IsAmountPaid, TravelID, CustomerID) Note: Essential Key - Underline Outside Key - Italics Social Schema: CustomerType (CustomerTypeID, CustomerTypeName) Essential Key (CustomerTypeID) Organization (CompanyID, CompanyName, Address, Phone, Fax, Email) Essential Key (CompanyID) Client (CustomerID, FirstName, LastName, Address, City, State, CompanyID, Mobile, PostCode, Email, CustomerTypeID) Essential Key (CustomerID), Outside Key (CompanyID) References Company (CompanyID), Outside Key (CustomerTypeID) References CustomerType (CustomerTypeID) Staff (StaffID, StaffName, Address, Phone, Email, SupervisorID, CompanyID) Essential Key (StaffID), Outside Key (SupervisorID) References Staff (StaffID), Outside Key (CompanyID) References Company (CompanyID) VIPStatus (VIPStatusID, VIPStatusName, DiscountPtge) Essential Key (VIPStatusID) Celebrity (CustomerID, VIPStatusID) Essential Key (CustomerID), Outside Key (CustomerID) References Customer (CustomerID) Outside Key (VIPStatusID) References VIPStatus (VIPStatusID) FrequentFlyer (CustomerID, FFDiscount) Essential Key (CustomerID), Outside Key (CustomerID) References Customer (CustomerID) Area (LocationID, LocationName, State, City, PostCode) Essential Key (LocationID) Flight (FlightID, FlightName) Essential Key (FlightID) Inns (HotelID, HotelName, LocationID, Phone) Essential Key (HotelID), Outside Key (LocationID) References Location (LocationID) TravelPackage (TravelPackageID, Description, PackageAmount, DatePkgIssued, DatePkgExpired, DepartingDate, ReturnDate) Essential Key (TravelPackageID) Aircraft (AirlineID, AirlineName, DepartureLocationID, DestinationLocationID, FlightID, DateOfDeparture, DateOfArrival, CabinClass, AirlineCharges) Essential Key (RestaurantID), Outside Key (DepartureLocationID) References Location (LocationID), Outside Key (DestinationLocationID) References Location (LocationID), Outside Key (FlightID) References Flight (FlightID) TravelBooking (TravelID, DepartureDate, ArrivalDate, OverallTravelAmount, CustomerID, StaffID, CompanyID, NumAdult, NumChild) Essential Key (TravelID), Outside Key (CustomerID) References Customer (CustomerID), Outside Key (StaffID) References Staff (StaffID), Outside Key (CompanyID) References Company (CompanyID) TravellingDetails (TravelPackageID, AirlineID, TravelID) Essential Key (TravelPackageID, AirlineID, TravelID), Outside Key (TravelPackageID) References TravelPackage (TravelPackageID), Outside Key (AirlineID) References Airline (AirlineID), Outside Key (TravelID) References TravelBooking (TravelID) StayAt (HotelID, TravelID, TravelPackageID) Essential Key (TravelPackageID, AirlineID, TravelID), Outside Key (TravelPackageID) References TravelPackage (TravelPackageID), Outside Key (HotelID) References Hotels (HotelID), Outside Key (TravelID) References TravelBooking (TravelID) Receipt (InvoiceID, InvoiceDate, TravelPackageAmount, IsAmountPaid, TravelID, CustomerID) Essential Key (InvoiceID), Outside Key (TravelID) References TravelBooking (TravelID), Outside Key (CustomerID) References Customer (CustomerID) Database Schema: DROP TABLE IF EXISTS CustomerType ; Make TABLE IF NOT EXISTS CustomerType ( CustomerTypeID INT NOT NULL, CustomerTypeName VARCHAR(45) NOT NULL, Essential KEY (CustomerTypeID)); - - - - - Table Company - - - - DROP TABLE IF EXISTS Company ; Make TABLE IF NOT EXISTS Company ( CompanyID INT NOT NULL, CompanyName VARCHAR(45) NOT NULL, Address VARCHAR(60) NOT NULL, Telephone VARCHAR(15) NOT NULL, Fax VARCHAR(15) NOT NULL, Email VARCHAR(45) NOT NULL, Essential KEY (CompanyID)); - - - - - Table Customer - - - - DROP TABLE IF EXISTS Customer ; Make TABLE IF NOT EXISTS Customer ( CustomerID INT NOT NULL, FirstName VARCHAR(25) NOT NULL, LastName VARCHAR(25) NOT NULL, Address VARCHAR(60) NOT NULL, City VARCHAR(30) NOT NULL, State VARCHAR(3) NOT NULL, Portable VARCHAR(15) NOT NULL, PostCode INT NOT NULL, Email VARCHAR(45) NOT NULL, CustomerTypeID INT NOT NULL, CompanyID INT NOT NULL, Essential KEY (CustomerID), List fk_Customer_CustomerType_idx (CustomerTypeID ASC), List fk_Customer_Company1_idx (CompanyID ASC), Requirement fk_Customer_CustomerType Remote KEY (CustomerTypeID) REFERENCES CustomerType (CustomerTypeID) ON DELETE NO ACTION ON UPDATE NO ACTION, Requirement fk_Customer_Company1 Remote KEY (CompanyID) REFERENCES Company (CompanyID) ON DELETE NO ACTION ON UPDATE NO ACTION); - - - - - Table Staff - - - - DROP TABLE IF EXISTS Staff ; Make TABLE IF NOT EXISTS Staff ( StaffID INT NOT NULL, StaffName VARCHAR(45) NOT NULL, Address VARCHAR(60) NOT NULL, Telephone VARCHAR(15) NOT NULL, Email VARCHAR(45) NOT NULL, CompanyID INT NOT NULL, SupervisorID INT NULL, Essential KEY (StaffID), List fk_Staff_Company1_idx (CompanyID ASC), List fk_Staff_Staff1_idx (SupervisorID ASC), Requirement fk_Staff_Company1 Remote KEY (CompanyID) REFERENCES Company (CompanyID) ON DELETE NO ACTION ON UPDATE NO ACTION, Requirement fk_Staff_Staff1 Remote KEY (SupervisorID) REFERENCES Staff (StaffID) ON DELETE NO ACTION ON UPDATE NO ACTION); - - - - - Table VIPStatus - - - - DROP TABLE IF EXISTS VIPStatus ; Make TABLE IF NOT EXISTS VIPStatus ( VIPStatusID INT NOT NULL, VIPStatusName VARCHAR(45) NOT NULL, DiscountPtge FLOAT NOT NULL, Essential KEY (VIPStatusID)); - - - - - Table VIP - - - - DROP TABLE IF EXISTS VIP ; Make TABLE IF NOT EXISTS VIP ( CustomerID INT NOT NULL, VIPStatusID INT NOT NULL, File fk_VIP_Customer1_idx (CustomerID ASC), File fk_VIP_VIPStatus1_idx (VIPStatusID ASC), Essential KEY (CustomerID), Imperative fk_VIP_Customer1 Outside KEY (CustomerID) REFERENCES Customer (CustomerID) ON DELETE NO ACTION ON UPDATE NO ACTION, Imperative fk_VIP_VIPStatus1 Outside KEY (VIPStatusID) REFERENCES VIPStatus (VIPStatusID) ON DELETE NO ACTION ON UPDATE NO ACTION); - - - - - Table FrequentFlyer - - - - DROP TABLE IF EXISTS FrequentFlyer ; Make TABLE IF NOT EXISTS FrequentFlyer ( CustomerID INT NOT NULL, FFDiscount FLOAT NOT NULL, File fk_FrequentFlyer_Customer1_idx (Cus