mySQL database

BCIS 4301 Lab Assignment #1 Data Models and the Relational DB Model 1. You have been selected to create a new database for a small summer camp in Vermont. The database will track campers, their registration, their payment, their bunk assignment, and their top three preferred activities. The database should also track camp counselors, their bunk assignments (one counselor to each camper bunkhouse), and their top three activities they supervise. a) List what tables you think will be needed for this project. b) Discuss what an entity will be in each table. I'll give you one as an example.....A camper will be an entity in the camper table. c) Identify at least three attributes for each entity. Continuing with the example, a camper will have a first name, last name, address, city, state, zip code, home phone, gender, age, etc. d) Define which relationships exist between tables. For example, a camper will be related to a bunkhouse. e) Determine if relationships are 1 to 1,1 to Many or Many to Many. A camper can only be in one bunkhouse but a bunkhouse will have many campers. f) Now take a closer look at your Many to Many relationships The relational database model cannot support this type of relationship so you have to break them into two tables with a linking table in between. Explain how you will correct these many to many relationships in your database. For example, a child can have many parents in the parent table ( a mom, a dad, a stepmom, etc.) and a parent can have many children at the camp (can send two siblings). So I would need a separate parent table from the camper table and I would have a linking table in between that had the parent id and the camper_id together. g) For each table, write the shorthand to represent each table used throughout the chapter. Put the table name and then in parentheses, list the attributes. Underline the field that you will be using as the primary key. There's a better example in chapter 4 on page 115 that explains the system they've been using in chapter 3! h) Write at least two business rules that will act as constraints on the problem. For example, a camper must be at least 8 years old to attend camp. 2. Your next project entails your company creating a database for a small music store. The store sells products to customers, so you will need a basic sales database that allows customers to buy instruments, sheet music, supplies, etc. In addition to selling products, the company also arranges music lessons with local teachers. There are currently 32 teachers that teach a variety of instruments and charge different hourly rates for their lessons. Some teachers only teach one instrument while other teachers may offer lessons on multiple instruments. Lessons should be incorporated in the sales database as a product so customers can "order" them. Repeat steps a-h from question #1. 3. Create a data dictionary for one of the projects above. Use the example on p.88 in the textbook for format. 4. Create a relational diagram for one of your projects above. See the example on on page 96, Figure 3.26 page 98 or Figure 3.30 You can use a database tool for this, you can use Microsoft Visio if BCIS 4301 Lab Assignment #1 Data Models and the Relational DB Model 1. You have been selected to create a new database for a small summer camp in Vermont. The database will track campers, their registration, their payment, their bunk assignment, and their top three preferred activities. The database should also track camp counselors, their bunk assignments (one counselor to each camper bunkhouse), and their top three activities they supervise. a) List what tables you think will be needed for this project. b) Discuss what an entity will be in each table. I'll give you one as an example.....A camper will be an entity in the camper table. c) Identify at least three attributes for each entity. Continuing with the example, a camper will have a first name, last name, address, city, state, zip code, home phone, gender, age, etc. d) Define which relationships exist between tables. For example, a camper will be related to a bunkhouse. e) Determine if relationships are 1 to 1,1 to Many or Many to Many. A camper can only be in one bunkhouse but a bunkhouse will have many campers. f) Now take a closer look at your Many to Many relationships The relational database model cannot support this type of relationship so you have to break them into two tables with a linking table in between. Explain how you will correct these many to many relationships in your database. For example, a child can have many parents in the parent table ( a mom, a dad, a stepmom, etc.) and a parent can have many children at the camp (can send two siblings). So I would need a separate parent table from the camper table and I would have a linking table in between that had the parent id and the camper_id together. g) For each table, write the shorthand to represent each table used throughout the chapter. Put the table name and then in parentheses, list the attributes. Underline the field that you will be using as the primary key. There's a better example in chapter 4 on page 115 that explains the system they've been using in chapter 3! h) Write at least two business rules that will act as constraints on the problem. For example, a camper must be at least 8 years old to attend camp. 2. Your next project entails your company creating a database for a small music store. The store sells products to customers, so you will need a basic sales database that allows customers to buy instruments, sheet music, supplies, etc. In addition to selling products, the company also arranges music lessons with local teachers. There are currently 32 teachers that teach a variety of instruments and charge different hourly rates for their lessons. Some teachers only teach one instrument while other teachers may offer lessons on multiple instruments. Lessons should be incorporated in the sales database as a product so customers can "order" them. Repeat steps a-h from question #1. 3. Create a data dictionary for one of the projects above. Use the example on p.88 in the textbook for format. 4. Create a relational diagram for one of your projects above. See the example on on page 96, Figure 3.26 page 98 or Figure 3.30 You can use a database tool for this, you can use Microsoft Visio if