Physical Data Model
Instructions
- Create a physical data model for the medium-sized business schema you created in Assignment 4. Include the following:
- Data types for all attributes with NULL or NOT NULL constraint.
- Resolution of any supertype/subtype relationships in the logical model (implement as-is, collapse into subtypes only, or roll up into supertype).
- A list of any added indexes (beyond the primary key indexes)mustbe included.
- Which NoSQL database implementations do you think would be good choices for the schema described in the textbook in Try This Exercise 8-1 and why? Please recommend at least two NoSQL database implementations.
Assignment Question Data Model Instructions Create a data model for a medium-sized business that uses the common patterns discussed in this module. Use an entity relationship diagram to document your model. Your solution must be normalized to third normal form as described in Module 3 (Topic 3.2)(which basically talks about the normalization process, 1NF, 2NF, 3NF) (Normalization is a best practice for databases used for business transactions.) Include the following entities and attributes (listed in parentheses): · Employees (Employee ID, Given Name, Middle Name, Family Name, Birth Date, Pay Grade Code, Pay Grade Name. Home Phone, Business Phone, Mobile Phone, Home Address) · Customers: · Individual Customers (Customer ID, Customer Given Name, Customer Family Name, Business Phone, Mobile Phone, Billing Address, Mailing Address, Shipping Address, Work E-Mail Address) · Organizational Customers (Customer ID, Customer Name, Business Phone, Billing Address, Mailing Address, Shipping Address, E-Mail Address, Web URL) · Suppliers, which can also be customers. (Supplier ID, Supplier Name, Business Phone, Mobile Phone, Billing Address, Mailing Address, Shipping Address) · Organizational Structure · Division (Division ID, Division Name, Division Manager Employee ID, Division Manager Name) · Department (Department ID, Department Name, Division ID, Division Name, Department Manager Employee ID, Department Manager Name) · Work Group (Work Group ID, Work Group Name, Department ID, Department Name, Work Group Leader Employee ID, Work Group Leader Name, List of Employees Assigned to Work Group) · Contact Methods – Generalize all the phone numbers, e-mail addresses, and home/shipping/mailing/billing addresses into common Contact Methods. · Purchase Orders (Supplier ID, Order Date, Purchase Order Number, Item Description, Quantity, Price Each, Item Subtotal [Quantity * Price Each], Order Status Code) Note: All physical addresses include Address Line 1, Address Line 2, City, State/Province, Country, Postal Code Prefer to use some data modelling and drawing tools like Er Win, ER/Studio, Power Designer, MySQL Workbench, SQL Editor and so on. Answer: ER Diagram The small business database design is as follows. 15 Tables are identified for this requirement. Table Names 1. AddressType 2. Addresses 3. ContactMethods 4. ContactMethods_Address_Map 5. Employees 6. CustomerType 7. Customers 8. Division 9. Department 10. WorkGroup 11. WorkGroup_Employees_Map 12. Suppliers 13. OrderStatus 14. PurchaseOrders 15. OrderItems Table Details Table Name AddressType Column Name Type Keys AddressTypeID Int PK_AddressType AddressType Varchar(100) Table Name Addresses Column Name Type Keys AddressID Int PK_Addresses AddressLine1 Varchar(100) AddressLine2 Varchar(100) City Varchar(100) State Varchar(100) Country Varchar(100) PostalCode Varchar(10) AddressTypeID int FK_Addresses_AddressType Table Name ContactMethods Column Name Type Keys ContactMethodID Int PK_ContactMethods HomePhone Varchar(50) BusinessPhone Varchar(50) MobilePhone Varchar(50) EMailAddress Varchar(200) Table Name ContactMethods_Address_Map Column Name Type Keys ContactMethods_Address_MapID Int PK_ContactMethods_Address_Map ContactMethodID Int FK_ContactMethods_Address_Map_Addresses AddressID Int FK_ContactMethods_Address_Map_ContactMethods Table Name Employees Column Name Type Keys EmployeeID Int PK_Employees GivenName Varchar(100) MiddleName Varchar(100) FamilyName Varchar(100) BirthDate Date PayGradeCode Varchar(100) ContactMethodID Varchar(100) FK_Employees_ContactMethods Table Name CustomerType Column Name Type Keys CustomerTypeID Int PK_CustomersType CustomerType Varchar(50) Table Name Customers Column Name Type Keys CustomersID Int PK_Customers CustomerTypeID Int FK_Customers_CustomerType CustomerName Varchar(200) CustomerFamilyName Varchar(200) ContactMethodID Int FK_Customers_ContactMethods Table Name Division Column Name Type Keys DivisionID Int PK_Division DivisionName Varchar(50) DivisionManager_EmployeeID Int FK_Division_Employees Table Name Department Column Name Type Keys DepartmentID Int PK_Department DepartmentName Varchar(100) DivisionID Int FK_Department_Division Table Name WorkGroup Column Name Type Keys WorkGroupID Int PK_WorkGroup WorkGroupName Varchar(100) DepartmentID Int FK_WorkGroup_Department WorkGroupLeader_EmployeeID Int FK_WorkGroup_Employees Table Name WorkGroup_Employees_Map Column Name Type Keys WorkGroup_Employees_MapID Int PK_WorkGroup_Employees_Map WorkGroupID Int FK_WorkGroup_Employees_Map_Employees EmployeeID Int FK_WorkGroup_Employees_Map_WorkGroup Table Name Suppliers Column Name Type Keys SupplierID Int PK_Suppliers SupplierName Varchar(100) ContactMethodID Int FK_Suppliers_ContactMethods Table Name OrderStatus Column Name Type Keys OrderStatusID Int PK_OrderStatus OrderStatusCode Varchar(50) OrderStatusName Varchar(50) Table Name PurchaseOrder Column Name Type Keys PurchaseOrderID Int PK_PurchaseOrders SupplierID Int FK_PurchaseOrders_Suppliers OrderDate Date PurchaseOrderNumber Varchar(50) OrderStatusID Int FK_PurchaseOrders_OrderStatus Table Name OrderItems Column Name Type Keys OrderItemsID Int PK_OrderItems PurchaseOrderID Int FK_OrderItems_PurchaseOrders ItemDescription Varchar(200) Quantity Float PriceEach Float SubTotal Float Computed Column (Quantity * PriceEach)