Question

�Martial Arts R Us� (MARU) needs a database. MARU is a martial arts school with hundreds of students. It is necessary to keep track of all the different classes that are being offered, who is assigned to teach each class, and which students attend each class. Also, it is important to track the progress of each student as they advance. Create a complete Crow�s Foot ERD for these requirements: Students are given a student number when they join the school. This is stored along with their name, date of birth, and the date they joined the school. � All instructors are also students, but clearly, not all students are instructors. In addition to the normal student information, for each instructor, the date that they start working as an instructor must be recorded, along with their instructor status (compensated or volunteer). � An instructor may be assigned to teach any number of classes, but each class has one and only one assigned instructor. Some instructors, especially volunteer instructors, may not be assigned to any class. � A class is offered for a specific level at a specific time, day of the week, and location. For example, one class taught on Mondays at 5:00 pm in Room #1 is an intermediate-level class. Another class taught on Mondays at 6:00 pm in Room #1 is a beginner-level class. A third class taught on Tuesdays at 5:00 pm in Room #2 is an advanced-level class. � Students may attend any class of the appropriate level during each week so there is no expectation that any particular student will attend any particular class session. Therefore, the actual attendance of students at each individual class meeting must be tracked. � A student will attend many different class meetings; and each class meeting is normally attended by many students. Some class meetings may have no students show up for that meeting. New students may not have attended any class meetings yet. � At any given meeting of a class, instructors other than the assigned instructor may show up to help. Therefore, a given class meeting may have several instructors (a head instructor and many assistant instructors), but it will always have at least the one instructor that is assigned to that class. For each class meeting, the date that the class was taught and the instructors� roles (head instructor or assistant instructor) need to be recorded. For example, Mr. Jones is assigned to teach the Monday, 5:00 pm, intermediate class in Room #1. During one particular meeting of that class, Mr. Jones was present as the head instructor and Ms. Chen came to help as an assistant instructor. � Each student holds a rank in the martial arts. The rank name, belt color, and rank requirements are stored. Each rank will have numerous rank requirements. Each requirement is considered a requirement just for the rank at which the requirement is introduced. Every requirement is associated with a particular rank. All ranks except white belt have at least one requirement. � A given rank may be held by many students. While it is customary to think of a student as having a single rank, it is necessary to track each student�s progress through the ranks. Therefore, every rank that a student attains is kept in the system. New students joining the school are automatically given a white belt rank. The date that a student is awarded each rank should be kept in the system. All ranks have at least one student that has achieved that rank at some time. Please provide the Crow's Foot ERD meeting these requirements, thank you.

Answer 1

Answer: Crow Foot ERD diagram: STUDENT SID Sname DOB, JOIN DATE INSTRUCTORS IID SID IJOIN_DATE STATUS CLASS CID IID DAY LOCATION LEVEL DESC MARTIA ACTIVITIES SID RANK, BELT COLOR MARTIAL REQID BELT COLOR, REQUIREMENTS ClassMeeting CMID CID IID STUDENT CLASSMEETINGS CMID, SID ATTENDENCE Explanation The crowfoot ERD diagram is implemented as per the given data provided. . Created a STUDENT table with the necessary attributes . Created a INSTRUCTORS table with the necessary attributes . Created a CLASS table with the necessary attributes . Created a CLASSMEETING table with the necessary attributes . Created a MARITAL table with the necessary attributes . Created a MARITALACTIVITIES table with the necessary attributes . Created a STUDENTCLASSMEETING table with the necessary attributes

. All the entities are connected with the equivalentrelationship supplies by using the cofo otation ach tabe contains the primary and the foreign keys that are required for fulfilling the relationships. Instructor's cal also be the students and in some cases, one student will be instructed by many instructors . It states that not all of the students are the instructors, each instructor can be assigned to the class Student's attendance and each of the class meeting can also be tracked. .Students are tending any of the classes each student have many classes it states. . Likewise several attributes and the relationships are mentioned in the above crowfoot ERD diagram

Answer 2

Please rate this..

STUDENT ( SID, Sname, DOB, JOIN_DATE)

INSTRUCTORS( IID,SID,I_JOIN_DATE, STATUS)

CLASS ( CID, IID, DAY,LOCATION,LEVEL, DESC )

ClassMeeting ( CMID,CID,IID)

MARTIAL(REQID,BELT_COLOR,REQUIREMENTS)

MARTIALACTIVITIES(SID,RANK,BELT_COLOR)

STUDENTCLASSMEETINGS(CMID,SID,ATTENDENCE)

CREATE TABLE STUDENT

(

SID INT IDENTITY,

Sname VARCHAR(50) NOT NULL,

DOB DATETIME NOT NULL,

JOIN_DATE DATETIME NOT NULL,

PRIMARY KEY (SID)

)

CREATE TABLE INSTRUCTORS

(

IID INT IDENTITY NOT NULL,

SID INT,

I_JOIN_DATE DATETIME,

STATUS VARCHAR(1) NOT NULL

PRIMARY KEY (IID),

FOREIGN KEY INSTRUCTORS(SID) REFERENCES STUDENT(SID)

)

CREATE TABLE CLASS

(

CID INT IDENTITY,

IID INT,

DAY DATETIME,

LOCATION VARCHAR(10),

LEVEL INT,

DESC VARCHAR(100),

PRIMARY KEY CLUSTERED(CID,IID),

)

CREATE TABLE CLASSMEETING

(

CMID INT IDENTITY,

CID INT,

IID INT

PRIMARY KEY(CMID),

FOREIGN KEY CLASSMEETING(CID,IID) REFERENCES CLASS(CID,IID)

)

CREATE TABLE MARTIAL

(

REQID INT IDENTITY,

BELT_COLOR VARCHAR(30),

REQUIREMENTS VACRHAR(50)

PRIMARY KEY (REQID)

)

CREATE TABLE MARTIALACTIVITIES

(

SID, INT IDENTITY,

RANK INT IDENTITY,

VARCHAR(30),

BELT_COLOR VACRHAR(50)

PRIMARY KEY (RANK),

FOREIGN KEY MARTIALACTIVITIES(SID) REFERENCES STUDENT(SID)

)

CREATE TABLE STUDENTCLASSMEETINGS(

CMID INT IDENTITY,

SID INT,

ATTENDENCE varchar(1),

FOREIGN KEY MARTIALACTIVITIES(CMID) REFERENCES CLASSMEETING(CMID)

)

Answer 3

Answers:

Notice that the ?gure includes surrogate keys for RANK, REQUIREMENT, and MEETING because thenatural keys did not meet the requirements for a good primary key.

The most common areas for confusion among students on this particular case surround attendance in the classmeetings. Students tend to think of relationship between CLASS and STUDENT similar to the M:N enrollrelationship that they have seen throughout the textbook. In this case, however, the relationship is not anenrollment relationship – instead it is an attendance relationship. As described in the case, students do notenroll in any particular class. What must be tracked is the attendance for each individual class meeting.Therefore, the M:N relationship in this scenario is actually between the STUDENT and the individual class MEETING.


The case also provides an opportunity to reinforce the fact that subtypes inherit not only the attributes of thesupertype but also the relationships. One requirement of the case is that the system must be able to trackwhich instructors actually taught each class meeting. There is already a M:N relationship between STUDENTand MEETING that can be implemented with the ATTENDANCE bridge entity using only the Stu_Num andMeet_Num attributes. Students should consider that because INSTRUCTOR is a subtype of STUDENT,instructors are already associated in a M:N relationship with MEETING through that same bridge. By addingthe Attend_Role attribute to ATTENDANCE, the bridge entity can properly track all students in a given classmeeting and record what role they played in that meeting (e.g. student, assistant instructor, or head instructor).

Finally, it is worth pointing out to the students that requirements are described as being an attribute of a rank.Some students will immediate consider requirements to be an entity, while others will model requirement as anattribute of the RANK entity. Considering rank requirements to be an attribute of RANK is perfectlyacceptable – however, it must be noted that as such rank requirements would be a multi-valued attribute.Therefore, the preferred implementation of a multi-valued attribute (creating a new entity for the multi-valuedattribute) would result in the creation of the REQUIREMENT table anyway.