Create the SQL statements needed to create the following tables and relationship

Question

Create the SQL statements needed to create the following tables and relationships. Please note the primary and foreign keys, nullable and non-nullable fields, and the data types. Look at page 254, figure 6-6 for an example of what is needed. On page 253, figure 6-5 gives a more general syntax of the create table statement needed for this assignment. You also may use MySQL Workbench to help create these statements for you. Please submit the SQL statements.

STUDENT STUDENT ID INT N STUDENT _NAME VARCHAR(150) NN REGISTRATION STUDENT_ID INT N CAMPUS-ADDRESS VARCHAR (400) NN GRADE CHAR(1) NN MAJOR VARCHAR(45) NN Indexes 0 0 INSTRUCTOR INSTRUCTOR_ID INT N NAME VARCHAR( 150) NN Oo COURSE COURSE_ID CHAR(6) N TITLE VARCHAR( 100) NN LOCATION VARCHAR(45) INSTRUCTORINSTRUCTOR ID INT - 0 0

Explanation / Answer

Hi, in MySql database the syntax for creating a table is

Syntax:

Now, creating the tables which you have given

Query for creating the Stuednt table.

CREATE TABLE STUDENT(
STUDENT_ID INT NOT NULL,
STUDENT_NAME VARCHAR(150) NOT NULL,
CAMPUS_ADDRESS VARCHAR(400) NOT NULL,
MAJOR VARCHAR(45) NOT NULL,
PRIMARY KEY (STUDENT_ID)
);

Query for creating the Registration table

CREATE TABLE REGISTRATION(
STUDENT_ID INT NOT NULL,
COURSE_ID CHAR(6) NOT NULL,
GRADE CHAR(1) NOT NULL,
FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT(STUDENT_ID)
);

Query for creating the Course table

CREATE TABLE COURSE(
COURSE_ID CHAR(6) NOT NULL,
TITLE VARCHAR(100) NOT NULL,
INSTRUCTOR_ID INT,
FOREIGN KEY (COURSE_ID) REFERENCES REGISTRATION(COURSE_ID)
);

Query for creating the Instructor table.
CREATE TABLE INSTRUCTOR(
INSTRUCTOR_ID INT NOT NULL,
NAME VARCHAR(150) NOT NULL,
LOCATION VARCHAR(45),
FOREIGN KEY (INSTRUCTOR_ID) REFERENCES COURSE(INSTRUCTOR_ID)
);

Please do comment in case of queries. Thanks!!!

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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