Data Models and the Relational DB Model

bafykbzaceb7ctfubikwswmj6quusewafylzwrpwvi4o57qwt4ezyprn7e5his (cloudflare-ipfs.com)

BCIS Lab Assignment 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
   you’re ready to start playing with that tool, you can use Word and the drawing tools, or you can
   hand draw it and send me a picture as an attachment. If you hand draw your diagram, make sure
   that all aspects of the diagram are legible.