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

my sql> create table employee

-> (fname varchar(15) not null,

-> Minit char,

-> Lname varchar(15) not null,

-> ssn char(9) not null,

-> Bdate date,

-> Address varchar(30),

-> Sex char,

-> Salary decimal(10,2),

-> Super_ssn char(9),

-> Dno int not null);

mysql> create table department

-> (Dname varchar(15) not null,

-> Dnumber int not null,

-> Mgr_ssn char(9) not null,

-> Mgr_start_date date,

-> Primary key (Dnumber),

-> Unique (Dname));

mysql> alter table employee add primary key (Ssn);

mysql> alter table employee add foreign key (Super_ssn) references employee(ssn);

mysql> alter table employee add foreign key (Dno) references department (Dnumber);

mysql> alter table department add foreign key (Mgr_ssn) 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.