Assignment 06 – Database Design/Implementation Assume you are responsible for organizing annual family reunions and need a database to keep track of the reunion information. First, you must collection...

look at file


Assignment 06 – Database Design/Implementation Assume you are responsible for organizing annual family reunions and need a database to keep track of the reunion information. First, you must collection information about all the family members. Assume you have each family member return information about themselves. There are adult singles and families with children. Enter all family members to be invited for future reunions. Family Member Survey data. Family Members: Family Survey: Document filled out by an adult Contact phone number: Contact email: Home address: Name F = father M = mother C = child A = single adult Food Allergies If a family member is single then one name would be entered on the survey. If it is a family then the father, mother and all children names are entered. The status of F indicates father, M is mother, C is child and A would be a single family member. Food allergies will be listed for all members. All data is required except the contact phone number and the food allergies. When a facility or location is selected then a Reunion date will be chosen. Schedule a Reunion Date and Location. Facility Scheduled Reunion Reserve Location Scheduled Reunion Form Reunion Name Location ID: (not to be filled out) Location name: Location Description: Location Address: Date of reunion: Food provided: Drinks provided: Single Cost: Family Cost: Everything is required on this form. Each family member will indicate if they are able to join in on the family reunion fun. Family Member Response to a reunion invite Reunion Responses Family: Reunion: Adult count: Child count: Date fee received: Fee amount: All fields are required. Step 1: List all the data elements needed for the Family Reunion data base. Step 2: Make sure each field is broken down to its smallest component. Current item Broken down into smaller components Step 3: Organize all elements within Tables (Use data flow diagram - entities and data stores are tables) Table: _______________________________________ Table: _______________________________________ Table: _______________________________________ Table: _______________________________________ Step 4: Determine the primary key and foreign keys for tables in Step 3. Step 5: Make sure each table structure is broken down to 3rd normal form. Step 6: Create a database structure using SSMS. More detail on the next page… 1. Using SSMS, create all tables with column names and data types for your database. Save the database as Assignment06_. 2. Create the table relationships (referential constraint) using the database diagram or code Alter Table statements. Here is an example of relating the Guitar Shop Orders table with the Customers table: ALTER TABLE [dbo].[Orders] WITH CHECK ADD FOREIGN KEY([CustomerID]) REFERENCES [dbo].[Customers] ([CustomerID]) 3. Create a script file that will create your database and all of your tables along with any constraints and save it to your local computer as_Assignment06.sql. Remove any code that sets database options. The only SQL statements needed in this script file is Create Database, Create Table, and any Alter statements that define column constraints. 4. Submit your script file _Assignment06.sql using the submission link located in the HFC Online course. You do not have to hand in pages 4 to 9, you only need to hand in the script file from step #4 using the submission link in the HFC online course. Page 2 of 2 Assignment 0 6 – Database Design/Implementation Page 1 of 2 Assume you are responsible for organizing annual family reunions and need a database to keep track of the reunion information . First, you must collection information about all the family members. Assume you have each family member return information about themselves. There are a dult singles and families with children. Family Survey : Document filled out by an adult Contact phone number: Contact email: Home address : Name F = father M = mother C = child A = single adult Food Allergies If a family member is single then one name would be entered on the survey . If it is a family then the father, mother and all children names are entered. The status of F indicates father, M is mother, C is child and A would be a single family member. Food allergies will be listed for all members. All data is required except the contact phone number and the food allergies. Enter all family members to be invited for future reun ion s . Family Member Family Members : Survey data. Assignment 06 – Database Design/Implementation Page 1 of 2 Assume you are responsible for organizing annual family reunions and need a database to keep track of the reunion information. First, you must collection information about all the family members. Assume you have each family member return information about themselves. There are adult singles and families with children. Family Survey: Document filled out by an adult Contact phone number: Contact email: Home address: Name F = father M = mother C = child A = single adult Food Allergies If a family member is single then one name would be entered on the survey. If it is a family then the father, mother and all children names are entered. The status of F indicates father, M is mother, C is child and A would be a single family member. Food allergies will be listed for all members. All data is required except the contact phone number and the food allergies. Enter all family members to be invited for future reunions. Family Member Family Members: Survey data.