Derive relations for the following conceptual data model:

Question

What will be an ideal response?

Answer 1

Customer (customerNo, customerName, customerStreet, customerCity,
customerState, customerZipCode, custTelNo, custFaxNo, DOB,
maritalStatus, creditRating)
Primary Key customerNo
Alternate Key custTelNo
Alternate Key custFaxNo
Employee (employeeNo, title, firstName, middleName, lastName, address, workTelExt,
homeTelNo, empEmailAddress, socialSecurityNumber, DOB, position, sex,
salary, dateStarted)
Primary Key employeeNo
Alternate Key socialSecurityNumber
Invoice (invoiceNo, dateRaised, datePaid, creditCardNo, holdersName, expiryDate,
orderNo, pMethodNo)
Primary Key invoiceNo
Foreign Key orderNo references Order(orderNo)
Foreign Key pMethodNo references PaymentMethod(pMethodNo)
Order (orderNo, orderDate, billingStreet, billingCity, billingState, billingZipCode,
promisedDate, status, customerNo, employeeNo)
Primary Key orderNo
Foreign Key customerNo references Customer(customerNo)
Foreign Key employeeNo references Employee(employeeNo)
OrderDetail (orderNo, productNo, quantityOrdered)
Primary Key orderNo, productNo
Foreign Key orderNo references Order(orderNo)
Foreign Key productNo references Product(ProductNo)
PaymentMethod (pMethodNo, paymentMethod)
Primary Key pMethodNo
Product (productNo, productName, serialNo, unitPrice, quantityOnHand, reorderLevel,

reorderQuantity, reorderLeadTime)

Primary Key productNo
Alternate Key serialNo
Shipment (shipmentNo, quantity, shipmentDate, completeStatus, orderNo, productNo,
employeeNo, sMethodNo)
Primary Key shipmentNo
Foreign Key orderNo, productNo references OrderDetail(orderNo, productNo)
Foreign Key employeeNo references Employee(employeeNo)
Foreign Key sMethodNo references ShipmentMethod(sMethodNo)
ShipmentMethod (sMethodNo, shipmentMethod)
Primary Key sMethodNo