I need help using CREATE TABLE statements in mySQL to create a company database.

Question

I need help using CREATE TABLE statements in mySQL to create a company database. The EMPLOYEE table has columns Fname, Minit, Lname, Ssn(underlined), Bdate, Address, Sex, Salary, Super_ssn, Dno. The DEPARTMENT table has columns Dname, Dnumber(underlined), Mgr_ssn, Mgr_start_date. The DEPT_LOCATIONS table has columns Dumber(underlined), Dlocation(underlined). THe PROJECT table has columns Pname, Pnumber(underlined), Plocation, Dnum. The WORKS_ON table has columns Essn(underlined), Pno(underlined), Hours. The DEPENDENT table has columns Essn(underlined), Dependent_name(underlined), Sex, Bdate, Relationship. You have to properly identify any primary key and foreign key constraints(use your best judgment). Any help would be awesome, thanks.

Explanation / Answer

employee101 is the name of the database

CREATE TABLE `employee101`.`Department` ( `Dname` VARCHAR(20) NOT NULL , `DNumber` INT(2) NOT NULL , `Mgr_Ssn` INT(10) NOT NULL , `Mgr_StartDate` DATE NOT NULL, PRIMARY KEY (`DNumber`(2)) );

CREATE TABLE `employee101`.`Employee` ( `Fname` VARCHAR(30) NOT NULL , `Minit` VARCHAR(30) NOT NULL , `Lname` VARCHAR(30) NOT NULL , `Ssn` INT(10) NOT NULL , `Bdate` DATE NOT NULL , `Address` TEXT NOT NULL , `Sex` VARCHAR(2) NOT NULL , `Salary` INT(7) NOT NULL , `Super_ssn` INT(10) NOT NULL , `Dno` INT(2) NOT NULL , PRIMARY KEY (`Ssn`(10)));

ALTER TABLE `employee` ADD FOREIGN KEY (`Dno`) REFERENCES `department`(`DNumber`);

CREATE TABLE `employee101`.`Dept_Locations` ( `DNumber` INT(2) NOT NULL , `DLocation` VARCHAR(30) NOT NULL , PRIMARY KEY (`DLocation`));

ALTER TABLE `dept_locations` ADD FOREIGN KEY (`DNumber`) REFERENCES `department`(`DNumber`);

CREATE TABLE `employee101`.`Project` ( `PName` VARCHAR(30) NOT NULL , `PNumber` INT(2) NOT NULL , `PLocation` VARCHAR(30) NOT NULL , `DNum` INT(2) NOT NULL , PRIMARY KEY (`PNumber`));

ALTER TABLE `project` ADD FOREIGN KEY (`DNum`) REFERENCES `department`(`DNumber`);

CREATE TABLE `employee101`.`Works_On` ( `ESsn` INT(10) NOT NULL , `Pno` INT(2) NOT NULL , `Hours` INT(3) NOT NULL , PRIMARY KEY (`ESsn`, `Pno`)) ;

ALTER TABLE `works_on` ADD FOREIGN KEY (`ESsn`) REFERENCES `employee`(`Ssn`) ;

ALTER TABLE `works_on` ADD FOREIGN KEY (`Pno`) REFERENCES `project`(`PNumber`);

CREATE TABLE `employee101`.`Dependent` ( `Essn` INT(10) NOT NULL , `DependentName` VARCHAR(30) NOT NULL , `Sex` VARCHAR(2) NOT NULL , `BDate` DATE NOT NULL , `Relationship` VARCHAR(30) NOT NULL , PRIMARY KEY (`Essn`, `DependentName`));

ALTER TABLE `dependent` ADD FOREIGN KEY (`Essn`) REFERENCES `employee`(`Ssn`);

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

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