"Assignment 3– Marking Guide/SheetCOMP1140 -2017 T1Marks: out of 150• Revised content of assignment 1 and assignment 2:(30) • Requirement Specification1. Requirement Scope:The commercial profit of Gourmet Pizza dis based on the orders made for an item which isplaced by the clients on the desktop of the business store. There are majorly two types of methodsto place an order by the customer.Generally it is by telephone” .Secondary it is by means of “walkin” clientsFor the first case, the client calls the business store by means of a telephone call to make an order for aitem. If the client is making an order for first time, the desktop employee need to genuine of the client ,the desktop makes a reverse call to the same number and takes the order. While taking the order, a newcustomer record is generated. If the call is not received, then it is reflected as hoax. The details oftelephone number along with date, duration of call, start time and end time is recorded into thedatabase. If the call is genuine, then the telephone is added to new customer record. In the secondary situation, the customer comes into the gourmet pizza and puts anorder for an item along with its size as specified within the menu.Requirement Specification:Gourmet Pizza does business based on basically few components … orders and clients who make theorders. The composite components for Gourmet Pizza are menu item, ingredients, payment mode,suppliers, hoax call details and lastly but least employee information along with the details of salarybased on hourly or shift basis. Gourmet Pizza will also store the information of ingredients which areadded to the item of menu. Menu consists of an item details with a selling price tag. Gourmet Pizza alsokeeps track of the inventory stock level of ingredients and also stores supplier’s details from where theingredients are purchased.The following specifications represent the requirements of Gourmet Pizza ? Order: It consist of the date on which order is made , order number, number of items and quantityof items, price per quantity of item, discount details if any applicable , payment method details inform of bank card , the customer details who made an order , type of order and status of the order.? Client: This gives the details about the customer like customer id, customer name, customeraddress, customer phone number.? Employee: Gourmet Pizza has two types of employees based on hourly basis and delivery basis.Gourmet stores the details of an employee such as employee id, first name, last name, address,telephone number, date of joining, tax pin number, salary, type of employee. ? Menu: In the menu, Gourmet Pizza maintain a list of items along with the item number, itemname, selling price, size.? Ingredients: The pizza store keeps information of ingredients in form of the ingredient code,name, type , present usage of the ingredients and the minimum level of the ingredients and thecode of the Supplier who supplies it.? Supplier: Gourmet Pizza requires ingredients to make pizza. These ingredients are supplied bysome business man named as suppliers here. Gourmet Pizza stores details of supplier such assupplier code, supplier name, supplier address, ingredients supplied code, quantity ofingredients supplied, total prize.To make the customers happy and increase the sales, Gourmet Pizza started a concept ofdiscountDiscount: It holds the value of discount code, scheme name, discount rate, start date, end dateand condition All the customer pay through cards .so the details of the cards are also stored which isassociated along with customer number and order number Pay mode: It holds the details of transaction made through a bank card. It consist ofcardnumber, bank code, customer number, amount.Bank : bank code, bank name, account name, customer name.The calls made by the customers are also recorded with following valuesCall: call date, phone number, start time, end time.Stock level : base level, current value, ingredient id ,supplier id • EER Diagram and Data Dictionary Data dictionaryA data dictionary is a collection of descriptions of the date objects or items in a data model forthe benefit of programmers and others who need to refer to them or the dictionary of data is at a timethe pillar of work and the result of research and analysis of data. It is just like a depicted picture of theentire work. This dictionary of data defines all categories of data or data types, brief the all essentialinformation about the software is included.Table DatatypeField name Constraint Descriptionname ,sizeOrderid Integer, 6 Primary key Identification number of orderOrderdate Date Not null Date on which order is madeOrderThe order is made by phone or walk- Type Varchar,20 Not nullin Customer number who has made theCustomerid Integer ,10 Foreign keyorderCustomer had ordered an item fromItemid Integer,5 Foreign keymenuDiscount given to the customer forDiscountidInteger, 5 Foreign key an item on certain terms andconditionsBased on the discount amt isDiscountamtDecimal(7,3) deductedUsed to collect the amount forBankcardidInteger ,15 Foreign keyordered items through a bankcardThe number of items purchased byItemquantityInteger,5 the customerDecimal Total amount after deduction ofTotal amnt(10,3) discountEmployee id used to keep track whoEmpidInteger ,7 Foreign keyworks on a particular order It is used to create a unique and notCustomeridInteger ,10 Primary key null field which distinguish betweentwo customersName of the customer who places anCustnameVarchar,20 Not nullCustomerorderAddress of the customer, it can beCustaddrVarchar,40 Not nullmultivalued attribute if neededCustphoneInteger ,15Phone number of the customer Identifies each and every item with aItemidInteger,5 Primary key unique number and it will be notemptyItemnameVarchar,20 Unique Name of the itemMenuSizeChar,6Size of the itemIngrdidInteger ,7 Foreign key Ingredients used in the item Selling price per item for a particularSellpriceDecimal 10,2 Not nullsize Its specifies the uniqueness of eachIngrdidInteger,7 Primary keyingredient which is also not nullIngredientsIngrdnameVarchar20 Unique Name of the ingredient Type of ingredient. Wet, dry, solid ,TypeVarchar10 liquidSupplier identification used toSupplieridInteger,7 Foreign key specify the supplier who supplies theingredientsThe volume of ingredients used toQuantityusedInteger,5 make an item which is been ordered.Date to know the volume ofDateDateingredient used on that particularday To know the discount details with itsDiscountidInteger,5 Primary keyunique numberDiscnameVarchar,20 Unique Name for the discount schemeDiscount If the number of items purchased,DisccondInteger,5 Check then discount can be executedThe amount of percentage to beDiscpercentDecimal 10,2 deducted from the total amount A unique number generated forSupplieridInteger ,7 Primary keyevery supplierSuplnameVarchar,29 Unique Name of the supplierSupladdrVarchar,29Address of the supplierSupplier The volume of ingredient supplied byQuantity Integer,7 the supplierPriceDecimal 20,4Price per each quantityTotalDecimal 30,5Total price1. Bank: bankcardid, bankid, bankname, bankadrr,accountnum, amount.Card number which is unique forBankcardidInteger, 15 Primary keyeach customer who holds itBankidInteger,10 Unique Every bank has its unique numberBanknameBankVarchar,26Name of the bankBankaddrVarchar,50Address of the bank.AccountnumInteger,15 Unique Accountnum for employeesShows the balance of the accountBalanceDecimal 30,5 Not nullnumber. Specification of the employee with aEmpidEmployeeInteger,7 Primary keycompany numberEname Varchar,25Name of the employeeAccountnum Integer,15Account number of the employeeWhether the employee works onType Varchar Not nullhourly or delivery basissCharge Decimal 10,2Charge per shiftDcharge Decimal 10,2Charger per deliveryLicenceidVarchar,25Licence of the employeeStotal Integer ,5Total number of shifts by a employeeTotal number of delivery by aDtotal Integer,5 employeeSalary Decimal 20,7Total salary of each employee Just a value to know the order ofinvidInventory Integer,7 Primary keyinventory checkedIngredient id whose stock level hasIngrdid Integer,7 Foreign keyto be knownBaselevel Integer,10 Not null The most minimum level of the stockThe total quantity existing on aTotalquantity Integer,10 particular dateUsedquantity Integer,10The quantity used for a dayCurrentIt will value after deduction of Integer,10 quantityusedquantity from total quantitypresentDate dateDate Date on which the purchase has torepurchasedate Date be made. Mode of communication to place anTypeCall details Varchar,20 order by customerDate DateDate on which call is made starttime Decimal 10,2Unique Starting time of the callEndtime Decimal 10,2 Unique Ending time of the callDuration Decimal 10,2 Not null Total time taken for taking an order• Transaction Requirements2. Transaction rules: A transaction symbolizes a unit of work performed within a database management system (orsimilar system) against a database, and treated in a coherent and reliable way independent ofother transactions. A transaction generally represents any change in database. Transaction ruledeals with ACID properties. The acid properties are atomicity, consistency, isolation anddurability.In case of Gourmet Pizza database, the acid properties are followed as per their respectivedefinition and real time implementation.1. Atomicity: It requires that each transaction be "all or nothing": if one part of thetransaction fails, then the entire transaction fails, and the database state is leftunchanged.Gourmet Pizza: If an order is place by means of the telephone. The authenticity of thecustomer is verified by calling back the same number and taking the order. If none replies,then it is considered as HOAX call which means nothing. If the order is placed by existingcustomer, then order is taken fully.2. Consistency: This property ensures that any transaction will bring the database from onevalid state to another. Any data written to the database must be valid according to alldefined business rules, including constraints, cascades, triggers and any combinationsthereof.Gourmet Pizza: when an order is placed, it should be according to the items and its sizespresent within the menu. A customer can place an order for more than one item. If acustomer deals to follow the certain rules of discount and payments .3. Isolation: This property ensures that the concurrent execution of transactions results in asystem state that would be obtained if transactions were executed sequentially.Gourmet Pizza: If the order has been taken either by phone or walk-in customersimultaneously, the delivery or execution of the order is done in a sequential form so that itdoesn’t create errors in the database.4. Durability: This property ensures that once a transaction has been committed, it will remain so,even in the event of power loss, crashes or errors Gourmet Pizza: if the order is placed by the customer, it need to be delivered at any costand if the order is placed then its mandatory that the payment should also be made by thecustomer.There are certain data manipulations on the queries of Gourmet Pizza database.1. Insertion and modification of tables such as update and delete on all the tables relatedto the database should be possible2. It is mandatory that the column of each table which holds the integrity constraint“primary key” property should have a value compulsory.3. All the column values of tables should be viewed based on the queries generated.4. The data should be inserted depending on the conditions of check constraint, on deletecascade and update cascade.5. The values inserted in the column names of each table should be according to its datatype and specified length.• Business Rules3. Business Rule Business Rules are the rules that define your business. A Business Rule is often a constraint or adescription of an operation within your business.A Business Rule must be atomic in other words, itsbeing able to be either true or false. The most important topic of Business Rules which must underlayany database Schema . These are often implemented as 'Constraints' in the Database.These Business Rules are important because they define the conditions that the Database must meet.? All orders that are generated by a telephone should be verified by making a callback to thecustomer.? The order of the item should be same as mentioned in the menu.? The discount rules and percentage will not be changed as per customer’s request but aredesigned and decided by the management of Gourmet Pizza.? The payment should be done by means of card.? The customers should be authentic and their address should be valid .? The Deliveries of the ordered item is done by the employees of the Gourmet Pizza.? The ingredients will be same in quantity and quality.? The Suppliers should supply the required ingredients as mentioned in the required format.? The Customer should have alteast one or many ORDERS.? The customer can be associated with only one ADDRESS.? The delivery is associated with just one order at a time? An order must be associated with one and only one CUSTOMER.? An order can be associated with one or many items of a menu.? A payment must be associated with one and only one ORDER but with many items . • Mapping of the EER model to the relational model in DBDL Relational data model is the primary data model, which is used widely for data storage and processing.This model is simple and it has all the properties and capabilities required to process data with storageefficiency.ConceptsTables - In relational data model, relations are saved in the format of Tables. This format stores therelation among entities. A table has rows and columns, where rows represent records and columnsrepresent the attributes.Tuple - A single row of a table, which contains a single record for that relation is called a tuple.Relation instance - A finite set of tuples in the relational database system represents relation instance.Relation instances do not have duplicate tuples.Relation schema - A relation schema describes the relation name (table name), attributes, and theirnames.Relation key - each row has one or more attributes, known as relation key, which can identify the rowin the relation (table) uniquely.Attribute domain - every attribute has some pre-defined value scope, known as attribute domain.ConstraintsEvery relation has some conditions that must hold for it to be a valid relation. These conditions arecalled Relational Integrity Constraints. There are three main integrity constraints -? Key constraints? Domain constraints? Referential integrity constraintsKey ConstraintsThere must be at least one minimal subset of attributes in the relation, which can identify a tupleuniquely. This minimal subset of attributes is called key for that relation. If there are more than onesuch minimal subset, these are called candidate keys.Key constraints force that - ? in a relation with a key attribute, no two tuples can have identical values for key attributes.? a key attribute cannot have NULL values.Key constraints are also referred to as Entity Constraints.Domain ConstraintsAttributes have specific values in real-world scenario. For example, salary can only be a positiveinteger. The same constraints have been tried to employ on the attributes of a relation. Every attributeis bound to have a specific range of values. For example, orderid can be created on item available andtelephone numbers cannot contain a digit outside 0-9.Referential integrity ConstraintsReferential integrity constraints work on the concept of Foreign Keys. A foreign key is a key attribute ofa relation that can be referred in other relation.Referential integrity constraint states that if a relation refers to a key attribute of a different or samerelation, then that key element must exist.For gourmet pizza, the database is classified in relational mode in the following table:Table name Candidate key Referential integrity Doman constraintsCustid,itemid,bankcardid, Type should not be nullOrderOrderiddiscountid,empid as it gives identify thegenuine order.Customer Custid - Customer name shouldbe notnullMenu Itemid IngrdidThe size domain shouldhave value in varcharIngredients Ingrdid Supplierid Ingredient namesshould be unique andnot emptySupplier id SupplieridQuantity should always be mentoned.Bankid should be inBank Bankcardid numbersDiscountDiscountidDiscount condition ispredefined but notmodify by a userEmployeeEmpidEmpname should bespecified.Specialization:The specialization is the abstracting process of introducing new characteristics to an existing class ofobjects to create one or more new classes of objects.Here, the employee table can be classified based on hourly shift and delivery shift workers.Weak entity: calldetails, discount, inventoryMulti valued attributes: address of the customer, first and last name of the employee, discountcondition.• Normalizing the relational schema to Boyce-Codd Normal Form Normalize relational schema to BCNF:Normalization: Normalization is a process of organizing the data in database to avoid data redundancy,insertion anomaly, update anomaly & deletion anomaly. Following are the general concept of the tables even though they are related , which comes into exist ifnormalization doesn’t take place.Order: order id, order date, customer id , customer name, customer address,customer_telephone_number, item num, item name, item size, item quantity, discount code, discountconditions, discount percentage, discount amount, payment card, bank name , bank address, bankaccount num, employee id, employee name.Customer: customer name , customer address, item no , item name, item ordered quantity, payment,telephone.Menu: item no , item name, size, ingredientsnum, ingredient name, ingredients quantity "