Make sure you have evaluated your functional dependencies and have resolved all First, Second and Third...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Make sure you have evaluated your functional dependencies and have resolved all First, Second and Third normal form violations (So your diagram should be in at least 3NF). 1) List the normalized relations (the logical design) and the attributes in each table and indicate the primary and foreign keys. Example: StudentTbl (StudentNumber, LastName, FirstName, Major, GPA, YearInSchool) If your design does not support full functional dependency, please include an explanation. 2) Include a data dictionary categorized by table (a list of your attributes with the data types and length defined for each). For example: Char/Text/String 28 3) Also include a list of attributes that are candidates for domain enforcement and the domains for at least 2 attributes. LastName Conceptual ERD Managed by Distributors "G" Guest Bottles/ Cans "D" "C" Used at "E" Event Guest Event Payment + Uses Cutomer Attends "p" Hosts (D) Product Customer places Cottage Guest Materials Paid by Cottage Vendor Provides Materials Order Ordered from Packing Fulfilled by Order Is made up Is part of Product Manages "C" is a Event Manager Fufills Full Time Grows at Grows Hop Crop Send out to "FT" Hop Variety Harvest Processes Employee Hops "E" Processors. D (D) "PT" Part Time Is from produces Ripens for "D" Suppervises Drivers Uses Works under Managed by Intern ** Manages Made of Is a Beer Made by "B" Brewers Crop Manager Beer Batch Is a part of Made up of Make 2:05 Back Feb 21, 2023 at 10:38 PM Customer CustomerID First Name Last Name address Shipping address phone email address Date of birth Assuming a guest can only be one of the following categories; Distributors (restaurants and breweries), Product customers (tasting room guests, product buyers, online shoppers), and Guests (cottagguestsst and event guests). Distributors Co Name Tax ID number Resale License number Shipping address Location Discount Assuming Distributors are the restaurants and Breweries. Product Customer Tasting room visits Date of birth Assuming Product customers are those who participate in tastings and purchase goods. Comments Submission Database Project-Part 1 Attribute and Assumption List for CCTC Dashboard Calendar Guest ID Date Assuming that a guest is either a cottage guest using VRBO or an Event guest and cannot be both at the same time. Cottage guest reservationID Duration of stay Room number Cottage RoomID Event ReservationID Number of attendees Location Event guest eventID Vendor VendorID Product SKU Type subcategory Files (1) 5 To Do a Employee EmployeelD Name position social security number start-date job title initial pay rate current pay rate address phone email address emergency contact information TIPS Certification Assuming employees can either be fulltime, part-time, or interns and interns cannot be considered part-time full- time. Full time Salary Assuming: Fulltime workers are paid salaries Part time Hours worked per week Intern requirement Graduation date hours Assuming hours are the internship requirements Rubric Notifications Inbox Brewers certification level certification date re-certification outcomes Drivers CDL number CDL expiration Event Manager eventID Assuming that only one manager manages each. event. Crop Manager Crop Location Hop Crop Location Name owner Size (acres) Hop Variety/type Plant date Hop Variety Name Alpha acid percentage Beta acid percentage Total oil HIS Myrcene Harvest harvestID Size (lbs) Moisture percentage Date Harvested Order TransactionID Product quantity Order number Order status Product list Payment Payment ID Type of payment date amount Beer name Quantity Aging method Aging duration Container Beer Batch BatchID name category Alc% volume year Packaged by Hops HopsID variety Processor ProcessorID Vendor id name address Phone number name of the principal contact Materials order id quantity price materials id Bottles/cans capacity shape color cost Database Project Part 1 Conceptual Data Model Members: Meead Alghamdi, Namratha Bhimavarapu, Julissa Bonilla The ColoCraft Trading Company (CCTC) is a small hospitality company, working farm, and craft brewery that sells hops, beer, and complementary products like meat and cheese to brokers, distributors, and breweries. They also host events and have four rooms in their Brewery Cottage that they use as short- term VRBO style rentals. CCTC currently employs 10 full-time employees, 12 part- time employees, and 3 interns. The business currently uses a combination of manual operations, Excel spreadsheets, and a dated order entry system for record-keeping, which has limited capabilities for hop farm and vendor management. To improve the record- keeping of their business, CCTC has invited several software or database consulting companies to evaluate their business needs and propose an automated solution to the record-keeping challenge. The proposed database will have modules for managing products, services, personnel, and brewery holdings and hop varieties. The database will keep track of the room and event reservations for guests and also track the certification and re-certification outcomes of employees. Additionally, the database will maintain a record of CCTC's hop farm and brewery holdings, including the location and size of each hop crop, the crop manager responsible for each crop, and the specific characteristics of each hop variety. Make sure you have evaluated your functional dependencies and have resolved all First, Second and Third normal form violations (So your diagram should be in at least 3NF). 1) List the normalized relations (the logical design) and the attributes in each table and indicate the primary and foreign keys. Example: StudentTbl (StudentNumber, LastName, FirstName, Major, GPA, YearInSchool) If your design does not support full functional dependency, please include an explanation. 2) Include a data dictionary categorized by table (a list of your attributes with the data types and length defined for each). For example: Char/Text/String 28 3) Also include a list of attributes that are candidates for domain enforcement and the domains for at least 2 attributes. LastName Conceptual ERD Managed by Distributors "G" Guest Bottles/ Cans "D" "C" Used at "E" Event Guest Event Payment + Uses Cutomer Attends "p" Hosts (D) Product Customer places Cottage Guest Materials Paid by Cottage Vendor Provides Materials Order Ordered from Packing Fulfilled by Order Is made up Is part of Product Manages "C" is a Event Manager Fufills Full Time Grows at Grows Hop Crop Send out to "FT" Hop Variety Harvest Processes Employee Hops "E" Processors. D (D) "PT" Part Time Is from produces Ripens for "D" Suppervises Drivers Uses Works under Managed by Intern ** Manages Made of Is a Beer Made by "B" Brewers Crop Manager Beer Batch Is a part of Made up of Make 2:05 Back Feb 21, 2023 at 10:38 PM Customer CustomerID First Name Last Name address Shipping address phone email address Date of birth Assuming a guest can only be one of the following categories; Distributors (restaurants and breweries), Product customers (tasting room guests, product buyers, online shoppers), and Guests (cottagguestsst and event guests). Distributors Co Name Tax ID number Resale License number Shipping address Location Discount Assuming Distributors are the restaurants and Breweries. Product Customer Tasting room visits Date of birth Assuming Product customers are those who participate in tastings and purchase goods. Comments Submission Database Project-Part 1 Attribute and Assumption List for CCTC Dashboard Calendar Guest ID Date Assuming that a guest is either a cottage guest using VRBO or an Event guest and cannot be both at the same time. Cottage guest reservationID Duration of stay Room number Cottage RoomID Event ReservationID Number of attendees Location Event guest eventID Vendor VendorID Product SKU Type subcategory Files (1) 5 To Do a Employee EmployeelD Name position social security number start-date job title initial pay rate current pay rate address phone email address emergency contact information TIPS Certification Assuming employees can either be fulltime, part-time, or interns and interns cannot be considered part-time full- time. Full time Salary Assuming: Fulltime workers are paid salaries Part time Hours worked per week Intern requirement Graduation date hours Assuming hours are the internship requirements Rubric Notifications Inbox Brewers certification level certification date re-certification outcomes Drivers CDL number CDL expiration Event Manager eventID Assuming that only one manager manages each. event. Crop Manager Crop Location Hop Crop Location Name owner Size (acres) Hop Variety/type Plant date Hop Variety Name Alpha acid percentage Beta acid percentage Total oil HIS Myrcene Harvest harvestID Size (lbs) Moisture percentage Date Harvested Order TransactionID Product quantity Order number Order status Product list Payment Payment ID Type of payment date amount Beer name Quantity Aging method Aging duration Container Beer Batch BatchID name category Alc% volume year Packaged by Hops HopsID variety Processor ProcessorID Vendor id name address Phone number name of the principal contact Materials order id quantity price materials id Bottles/cans capacity shape color cost Database Project Part 1 Conceptual Data Model Members: Meead Alghamdi, Namratha Bhimavarapu, Julissa Bonilla The ColoCraft Trading Company (CCTC) is a small hospitality company, working farm, and craft brewery that sells hops, beer, and complementary products like meat and cheese to brokers, distributors, and breweries. They also host events and have four rooms in their Brewery Cottage that they use as short- term VRBO style rentals. CCTC currently employs 10 full-time employees, 12 part- time employees, and 3 interns. The business currently uses a combination of manual operations, Excel spreadsheets, and a dated order entry system for record-keeping, which has limited capabilities for hop farm and vendor management. To improve the record- keeping of their business, CCTC has invited several software or database consulting companies to evaluate their business needs and propose an automated solution to the record-keeping challenge. The proposed database will have modules for managing products, services, personnel, and brewery holdings and hop varieties. The database will keep track of the room and event reservations for guests and also track the certification and re-certification outcomes of employees. Additionally, the database will maintain a record of CCTC's hop farm and brewery holdings, including the location and size of each hop crop, the crop manager responsible for each crop, and the specific characteristics of each hop variety.
Expert Answer:
Answer rating: 100% (QA)
Based on the instructions you provided here are steps to create a normalized database structure using the provided EntityRelationship Diagram ERD the ... View the full answer
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these databases questions
-
Mike is approved for life insurance. He meets with his agent to accept the policy and pay his first premium. A week later, Mike decides to exercise his right of rescission. What happens with regard...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Read the whitepaper below and summarize what the paper about and discuss the incident response steps the paper mentions and the recovery strategy and phases. Your summary is with 2-3 references and...
-
Develop two goals pertaining to your education, future career, or personal life. Each goal includes at least three objectives. Make sure your objectives include performance, conditions, and criteria....
-
From Prob. 4.15 the purely radial polar-coordinate flow which satisfies continuity is r = f ()/r, where f is an arbitrary function. Determine what particular forms of f() satisfy the full...
-
A number of substances containing positively polarized carbon atoms have been labeled as "cancer suspect agents" (i.e., suspected carcinogens or cancer-inducing compounds). It has been suggested that...
-
The air removal from the surface condenser leads to (a) fall in condenser pressure (b) rise in condenser pressure (c) no change in condenser pressure (d) rise in condenser temperature
-
According to the California Educational Code (www.cde.ca.gov/ (www.cde.ca.gov/ls/fa/sf/peguidemidhi.asp), ls/fa/sf/peguidemidhi.asp), students in grades 7 12 should receive 400 minutes of physical...
-
why it is important to have a good first impression and why you must stand out to the interviewer ? As a health care professional, what are the skills you can develop to build positive relationships...
-
Tanner Manufacturing is developing an activity-based costing system to improve overhead cost allocation. One of the first steps in developing the system is to classify the costs of performing...
-
Your task is to choose ONE of the videos and answer the following questions in paragraph form. The paragraph should answer all the questions below and be exactly 300 WORDS. Please use all of the...
-
Parallel would be best so when one light goes out the rest of the lights would still be on. E. What is a current? What is a voltage? What are the SI units of each?
-
discuss wind wave growth and wave propagation in coastal engineering.?
-
What is Dunham's Sports and what is their company history? What is Dunham's Sports proposed partnerships? What is Dunham's Sports' culture like and what is their mission development about? Make sure...
-
. Select one of the following types of service businesses in the Wake County Area. Examples in your EBOOK EX 17.14 Examples of Activities For Service Providers will help you get started. . PNC ARENA...
-
what are some of the main approaches to coastal engineering ( "saving the beach")
-
dy Consider the ordinary differential equation y, y(0) = 0. Then this differential equation have dx Unique solution infinitly many solution More than one but finitely many solution No solution
-
CLASS PERIO Solving Linear Equations: Variable on Both Sides Solve each equation. 1) 6r+ 7 = 13 + 7r 3) -7x-3x+2=-8x-8 5)-14 +66+7-26=1+5b 7) n-3n = 14-4n 2) 13-4x=1-x 4)-8-x= x - 4x 6)n+2=-14-n 8)...
-
In 2012, Lou has a salary of $54,000 from her job. She also has interest income of $1,700. Lou is single and has no dependents. During the year, Lou sold silver coins held as an investment for a...
-
Your supervisor has asked you to research the following situation concerning Owen and Lisa Cordoncillo. Owen and Lisa are brother and sister. In May 2012, Owen and Lisa exchange business pickup...
-
Sherry Hopson owns a retail family clothing store. Her store is located at 4321 Circle Drive, Houston, TX 77001. Her employer identification number is 95- 1234321 and her Social Security number is...
-
The convolution integral can be applied to solve nonlinear problems. Indicate whether the statement presented is true or false. If true, state why. If false, rewrite the statement to make it true.
-
A spring has a cubic nonlinearity which is an example of a (geometric, material) _______________________________ nonlinearity.
-
When a superharmonic resonance occurs, the free oscillation term does not decay exponentially but combines with the forced response. Indicate whether the statement presented is true or false. If...
Study smarter with the SolutionInn App