Starting from:
$30
CSCI3287-Homework 4 Key-Based, Fully Attributed, 3 NF Data Model Solved
This project will give you hands-on practice in working with MySQL Workbench (or similar tool) to create a key-based, fully attributed, 3 NF data model. In this project you will design a database, draw a data model to represent the design, then create a “physical model” of your design in the format of DDL (table create statements.)
You may use a “pair programming” approach on this assignment.
Objectives
1. Familiarize yourself with an unfamiliar company via the Case Study
2. Gain an understanding of the unfamiliar company’s data as presented in the Case Study
3. Use a data modeling tool to design a database to meet the needs of the client company
4. Use the data modeling tool to generate the DDL to create the database you have designed
Deliverables
1. A key-based, fully-attributed data model depicting your database design using the information in the case study as your input. Your model should include:
• All tables with primary key attributes defined
• All attributes with data type, length, and constraints defined
• Proper table names, key names and attribute names
• All relationships between tables showing captions both ways, and proper optionality and cardinality
2. The DDL necessary to create the database you have designed.
3. Documentation of any assumptions you made regarding unclear or missing requirements. For example:
• If you are creating surrogate keys, name the key and explain why you are creating the surrogate.
• Note the fact that you are using auto-increment for any created surrogate keys.
Case Study Scenario
For this assignment, you must read and analyze the Eden Landscaping Case Study. As you read the Case Study, you must pay close attention to every reference to the data that is collected and used by Eden Landscaping.
For this assignment, you must play the role of a consultant who has been hired by the client (Eden Landscaping) to design and create a database for them.
The SCOPE of this assignment includes your database design ONLY for the inventory kept by Eden for their LIVE PLANT STOCK and LANDSCAPING MATERIALS.
• The scope of this assignment specifically EXCLUDES the tracking of any items kept in inventory for the purpose of retail sales through Eden’s retail store.
• The scope of the assignment EXCLUDES any inventory of tools and equipment used by Eden’s crews as they go out to customers’ locations and do landscaping jobs.
Your scope for this project includes
• plants and landscaping materials that are sold to customers who come to the store, make a purchase, and pick up and carry their purchased items
• plants and landscaping materials that are sold to customers who come to the store, make a purchase, and have Eden deliver their purchased items to their home
• plants and landscaping materials that are sold to customers as part of a landscaping service contract (“job”), where Eden loads up a truck and sends a crew to the customer’s home to do a landscaping project
The case study includes in-depth descriptions of the business processes that center around the keeping of inventory, including business activities that ADD items into inventory (such as Orders and receiving Shipments) and the business activities that SUBTRACT items from inventory (such as sales, landscaping jobs, and deliveries of purchased items to customers.)
Your database design must support the following business processes at Eden
• the ordering of plants and landscaping materials from suppliers
• taking physical inventory to see what’s in stock
• receiving incoming shipments of plants and landscaping materials from suppliers
• fulfilling customer orders and purchases, including landscaping jobs
Step-By-Step Instructions
• Read the entire Case Study
• As you read the case study, you must document the ENTITIES and ATTRIBUTES you observe as you learn about how Eden keeps track of its inventory of LIVE STOCK and LANDSCAPING MATERIALS.
• As you read the case study, you must consider each business process that affects inventory. That is, consider the processes that ADD plants and materials into inventory. And, consider that processes that REMOVE plants and materials from inventory.
• As you finish identifying entities and attributes, then you can begin to design the Inventory Database that will become the foundation of the Inventory Tracking System that your consulting firm will build for Eden in the next phase of this project.
• Draw a data model that includes all entities and attributes.
• Walk through the data model to ensure that it contains all entities and attributes necessary to support he business processes described by the staff at Eden.
Your database design deliverables must include
• A Fully Attributed Logical Data Model o all entities must be properly named,
o all relationships must be properly named (at least one way), o all cardinality and optionality must be defined o all primary and foreign keys defined o all many-to-many relationships must be resolved o entities/attributes must be in third normal form
• A Physical Data Model (SQL create statements) showing all tables, column names, data types, lengths, along with primary and foreign key constraints.
You may use a “pair programming” approach on this assignment.
Objectives
1. Familiarize yourself with an unfamiliar company via the Case Study
2. Gain an understanding of the unfamiliar company’s data as presented in the Case Study
3. Use a data modeling tool to design a database to meet the needs of the client company
4. Use the data modeling tool to generate the DDL to create the database you have designed
Deliverables
1. A key-based, fully-attributed data model depicting your database design using the information in the case study as your input. Your model should include:
• All tables with primary key attributes defined
• All attributes with data type, length, and constraints defined
• Proper table names, key names and attribute names
• All relationships between tables showing captions both ways, and proper optionality and cardinality
2. The DDL necessary to create the database you have designed.
3. Documentation of any assumptions you made regarding unclear or missing requirements. For example:
• If you are creating surrogate keys, name the key and explain why you are creating the surrogate.
• Note the fact that you are using auto-increment for any created surrogate keys.
Case Study Scenario
For this assignment, you must read and analyze the Eden Landscaping Case Study. As you read the Case Study, you must pay close attention to every reference to the data that is collected and used by Eden Landscaping.
For this assignment, you must play the role of a consultant who has been hired by the client (Eden Landscaping) to design and create a database for them.
The SCOPE of this assignment includes your database design ONLY for the inventory kept by Eden for their LIVE PLANT STOCK and LANDSCAPING MATERIALS.
• The scope of this assignment specifically EXCLUDES the tracking of any items kept in inventory for the purpose of retail sales through Eden’s retail store.
• The scope of the assignment EXCLUDES any inventory of tools and equipment used by Eden’s crews as they go out to customers’ locations and do landscaping jobs.
Your scope for this project includes
• plants and landscaping materials that are sold to customers who come to the store, make a purchase, and pick up and carry their purchased items
• plants and landscaping materials that are sold to customers who come to the store, make a purchase, and have Eden deliver their purchased items to their home
• plants and landscaping materials that are sold to customers as part of a landscaping service contract (“job”), where Eden loads up a truck and sends a crew to the customer’s home to do a landscaping project
The case study includes in-depth descriptions of the business processes that center around the keeping of inventory, including business activities that ADD items into inventory (such as Orders and receiving Shipments) and the business activities that SUBTRACT items from inventory (such as sales, landscaping jobs, and deliveries of purchased items to customers.)
Your database design must support the following business processes at Eden
• the ordering of plants and landscaping materials from suppliers
• taking physical inventory to see what’s in stock
• receiving incoming shipments of plants and landscaping materials from suppliers
• fulfilling customer orders and purchases, including landscaping jobs
Step-By-Step Instructions
• Read the entire Case Study
• As you read the case study, you must document the ENTITIES and ATTRIBUTES you observe as you learn about how Eden keeps track of its inventory of LIVE STOCK and LANDSCAPING MATERIALS.
• As you read the case study, you must consider each business process that affects inventory. That is, consider the processes that ADD plants and materials into inventory. And, consider that processes that REMOVE plants and materials from inventory.
• As you finish identifying entities and attributes, then you can begin to design the Inventory Database that will become the foundation of the Inventory Tracking System that your consulting firm will build for Eden in the next phase of this project.
• Draw a data model that includes all entities and attributes.
• Walk through the data model to ensure that it contains all entities and attributes necessary to support he business processes described by the staff at Eden.
Your database design deliverables must include
• A Fully Attributed Logical Data Model o all entities must be properly named,
o all relationships must be properly named (at least one way), o all cardinality and optionality must be defined o all primary and foreign keys defined o all many-to-many relationships must be resolved o entities/attributes must be in third normal form
• A Physical Data Model (SQL create statements) showing all tables, column names, data types, lengths, along with primary and foreign key constraints.
1 file (115.8KB)
