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.