This article discusses the revised ERD and schema for a restaurant database, including the addition of a new entity Certificate and attributes to existing entities. The 3NF relational schema contains relations for Certificate, Restaurant, RestaurantCertification, Suburb, Customer, Driver, Dish, Order, and Ordered_Dish. The data dictionary provides details on the attributes of each table.
Revised ERD and Schema for a Restaurant Database
Added on 2019-09-21
BookmarkShareRelated Documents
Part 1: Revised ERD and Schema The revised ERD is, Following changes have been made in the revised ERD. The new entity Certificate has been added. It contains the details of the certifications of the restaurants. There is a many to many relationship between Restaurant and Certificate. So, the newassociative entity, RestaurantCertification has been added to the design. The suburb entity has been added. As suburb is an important part of the database, so keeping is asan entity will help in making queries based on suburb, adding relationships with other entities andreducing the chances of errors in the database. Attributes have been added to all entities to add more information. The 3NF relational schema contains the following relations mapped from the revised ERD. CERTIFICATE ( CertificateID, CertificateName)RESTAURANT (RestaurantID, RestaurantName, RestaurantDetails, Ethnicity, Street, SuburbID, State, Zipcode, PhoneNumber)RESTAURANT_CERTICATION (RestaurantID, CertificateID, DateCertification)
SUBURB(SuburbID, SuburbName)CUSTOMER (CustomerID, CustomerFirstName, CustomerLastName, PhoneNumber, Street, SuburbID, State, Zipcode)DRIVER(DriverID, DriverFirstName, DriverLastName, Availability, SuburbID)DISH(DishID, DishName, Description, PreparationType, MainIngredients, CourseType, EFRating, CarbonCost, Price, DeliveryTime, RestaurantID)ORDER (OrderID, DriverID, CustomerID, OrderDate, OrderDeliveryTime, ActualDeliveryTime, CurrentStatus)ORDERED_DISH (OrderID, DishID)Part 2: Data Dictionary The data dictionary is, TableAttributeBrief DescriptionDatatypeand SizeRequiredor NotConstraint(s)ReferentialIntegrityRules (ifAny)CertificateCertificateIDUnique certificate code.INTYesPrimary keyCertificateNameName of certification.VARCHAR2(50)YesRestaurantRestaurantIDUnique restaurant code.INTYesPrimary keyRestaurantNameName of a restaurant.VARCHAR2(50)YesRestaurantDetailsShort description about the restaurant.VARCHAR2(250)YesEthnicityEthnicity of the restaurant. VARCHAR2(50)YesStreetStreet details.VARCHAR2(250)YesSuburbIDSuburb IDINTYesForeign key On update StateStatecodeVARCHAR2(3)Yes {NSW, QLD, SA, TAS, VIC, WA}Zipcode4 digit zipcodeCHAR(4)Yes0000-9999PhoneNumber10 digit contact number of the restaurant. CHAR(10)Yes0000000000-9999999999Restaurant_CertificationRestaurantIDRestaurant codeINTYesPrimary key and foreign keyOn update CertificateIDCertificate codeINTYesPrimary key and foreign keyOn update
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Implementation Process for FF4U Online Food Delivery Systemlg...