Martial Arts R Us (MARU) needs a database. MARU is a martial arts school with hu

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 Crows 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 students 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.

Explanation / Answer

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)

)

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

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