title: MySQL Creating Tables from ER Diagram
body: <p>I am working on creating a database from an ER diagram however I don't think some of my primary and foreign keys are correct. The tables that I don't think they are correct in is the primary key in section and the FK and PK in enrolled table. I also don't think I am properly enforcing my FK constraints so that I can detect referential integrity violations. </p>
  
  <p>Here is the ER Diagram I am basing my database off of.</p>
  
  <p><img src="https://i.stack.imgur.com/kh9ne.jpg" alt="ER Diagram"></p>
  
  <p>Here are the tables that I have made</p>
  
  <pre><code>CREATE TABLE Student
  (
      StudentId INTEGER,
      FName VARCHAR(20),
      LName VARCHAR(20),
      DOB CHAR(10),
      Major VARCHAR(20),
      PRIMARY KEY(StudentId)
  );
  
  CREATE TABLE Phone
  (
      sID INTEGER,
      Pnumber CHAR(20),
      Type CHAR(3),
      PRIMARY KEY(Pnumber)
  );
  
  CREATE TABLE Class
  (
      ClassId VARCHAR(6),
      Description VARCHAR(30),
      NumCredits Integer,
      Prereq VARCHAR(20),
      PRIMARY KEY(ClassId)
  );
  
  CREATE TABLE Section
  (
      ClassId VARCHAR(6),
      SecNo CHAR(10),
      Semester CHAR(4),
      ClassRoom VARCHAR(6),
      TimeOffered VARCHAR(18),
      PRIMARY KEY(SecNo),
      FOREIGN KEY(ClassId) REFERENCES Class(ClassId)  
  );
  
  CREATE TABLE Enrolled
  (
      StudentId INTEGER,
      SecNum VARCHAR(40),
      ClassId VARCHAR(8),
      Semes VARCHAR(6),
      GorDD VARCHAR(30)
      FOREIGN KEY(ClassId) REFERENCES Section(ClassId),
      FOREIGN KEY(StudentId) REFERENCES Student(StudentId)
  );
  
  CREATE TABLE Professor
  (
      EmpId INTEGER,
      FName VARCHAR(10),
      LName VARCHAR(10),
      Dept VARCHAR(2),
      QualClass VARCHAR(40),
      PRIMARY KEY (EmpId)
  );
  
  CREATE TABLE Teaches
  (
      Class VARCHAR(5),
      Section INTEGER,
      Semester CHAR(4),
      EmpId INTEGER,
      FOREIGN KEY (EmpId) REFERENCES Professor(EmpId)
  );
  
  CREATE TABLE Qualified
  (
      EmpId INTEGER,
      ClassId VARCHAR(5)
  );
  </code></pre>
  
  <p>Thank you for the help in understanding how to create a DB from an ER diagram.</p>