Read the following description and then answer questions 1-5 at the end. The swi

Question

Read the following description and then answer questions 1-5 at the end.

The swim club database in Figure 9.17 has been designed to hold information about students who are

enrolled in swim classes. The following information is stored: students, enrollment, swim classes, pools

where classes are held, instructors for the classes, and various levels of swim classes. Use Figure 9.17 to

answer questions 1 to 5.

The primary keys are identified below. The following data types are defined in the SQL Server.

tblLevels

Level – Identity PK

ClassName – text 20 – nulls are not allowed

tblPool

Pool – Identity PK

PoolName – text 20 – nulls are not allowed

Location – text 30

tblStaff

StaffID – Identity PK

FirstName – text 20

MiddleInitial – text 3

LastName – text 30

Suffix – text 3

Salaried – Bit

PayAmount – money

tblClasses

LessonIndex – Identity PK

Level – Integer FK

SectionID – Integer

Semester – TinyInt

Days – text 20

Time – datetime (formatted for time)

Pool – Integer FK

Instructor – Integer FK

Limit – TinyInt

Enrolled – TinyInt

Price – money

tblEnrollment

LessonIndex – Integer FK

SID – Integer FK (LessonIndex and SID) Primary Key

Status – text 30

Charged – bit

AmountPaid – money

DateEnrolled – datetime

tblStudents

SID – Identity PK

FirstName – text 20

MiddleInitial – text 3

LastName – text 30

Suffix – text 3

Birthday – datetime

LocalStreet – text 30

LocalCity – text 20

LocalPostalCode – text 6

LocalPhone – text 10

Implement this schema in SQL Server or access (you will need to pick comparable data types). Submit a screenshot of your ERD in the database.

1. Explain the relationship rules for each relationship (e.g., tblEnrollment and tblStudents: A

student can enroll in many classes).

2. Identify cardinality for each relationship, assuming the following rules:

? A pool may or may not ever have a class.

? The levels table must always be associated with at least one class.

? The staff table may not have ever taught a class.

? All students must be enrolled in at least one class.

? The class must have students enrolled in it.

? The class must have a valid pool.

? The class may not have an instructor assigned.

? The class must always be associated with an existing level.

3. Which tables are weak and which tables are strong (covered in an earlier chapter)?

tblLevels Level ClassName tblClasses Lessonindex Level SectionID Semester Days Time Pool Instructo Limit Enrolled Price tblStudents tblEnrollment ?Lesson!ndex tblPool Pool PoolName Location SID Status Charged AmountPaid DateEnrolled FirstName MiddleInitial LastName LocalStreet Localcity LocalPostalCode LocalPhone tblStaff StaffID FirstName Middletnitial LastName Suffix Salaried PayAmoumt

Explanation / Answer

If you have any doubts, please give me comment...

CREATE TABLE tblLevels(
    Level INT NOT NULL PRIMARY KEY,
    ClassName VARCHAR(20) NOT NULL
);

CREATE TABLE tblPool(
    Pool INT NOT NULL PRIMARY KEY,
    PoolName VARCHAR(20) NOT NULL,
    Location VARCHAR(30)
);

CREATE TABLE tblStaff(
    StaffID INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(20),
    MiddleInitial VARCHAR(3),
    LastName VARCHAR(30),
    Suffix VARCHAR(3),
    Salaried BIT,
    PayAmount REAL(10,2)
);

CREATE TABLE tblClasses(
    LessonIndex INT NOT NULL PRIMARY KEY,
    Level INT,
    SectionID INT,
    Semester TINYINT,
    Days VARCHAR(20),
    Time DATETIME,
    Pool INT,
    Instructor INT,
    Limit TINYINT,
    Enrolled TINYINT,
    Price REAL(10,2),
    FORIEGN KEY(Level) REFERENCES tblLevels(Level),
    FORIEGN KEY(Pool) REFERENCES tblPool(Pool),
    FORIEGN KEY(Instructor) REFERENCES tblStaff(StaffID)
);

CREATE TABLE tblStudents(
    SID INT NOT NULL PRIMARY KEY,
    FirstName VARCHAR(20),
    MiddleInitial VARCHAR(3),
    LastName VARCHAR(30),
    Suffix VARCHAR(3),
    Birthday DATETIME,
    LocalStreet VARCHAR(30),
    LocalCity VARCHAR(20),
    LocalPostalCode CHAR(6),
    LocalPhone CHAR(10)
);

CREATE TABLE tblEnrollment(
    LessonIndex INT,
    SID INT,
    Status VARCHAR(30),
    Charged BIT,
    AmountPaid REAL(10,2),
    DateEnrolled DATETIME,
    PRIMARY KEY(LessonIndex, SID),
    FORIEGN KEY(LessonIndex) REFERENCES tblClasses(LessonIndex),
    FORIEGN KEY(SID) REFERENCES tblStudents(SID),
);

1)
tblEnrollment and tblClasses,
tblEnrollment and tblStudents: A student can enroll in many classes

tblClasses and tblLevels, tblClasses and tblPool, tblClasses and tblStaff

2)
A pool may or may not ever have a class. (1 - 1)
? The levels table must always be associated with at least one class.(1 to n)
? The staff table may not have ever taught a class.(1 to 1)
? All students must be enrolled in at least one class.(M to N)
? The class must have students enrolled in it.(M to N)
? The class must have a valid pool.(1 - M)
? The class may not have an instructor assigned.(1-1)
? The class must always be associated with an existing level.(1-1)

3) All are strong entities(tblLevels, tblPool, tblStaff, tblClasses, tblStudents)

4) Indentifying relationships: tblEnrollment

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.