Databases: MySQL & MariaDB - For part 1 of this assignment, create the following

Question

Databases: MySQL & MariaDB

- For part 1 of this assignment, create the following tables in the given order using MySQL:

Create a table called 'client' with the following columns:

id - an auto-incrementing integer which is the primary key, size 11

first_name - a varchar with a maximum length of 255 characters, cannot be null

last_name - a varchar with a maximum length of 255 characters, cannot be null

dob - a date type with no default and null not allowed

The combination of the first_nameand last_name must be unique in this table

Create a table called 'employee' with the following columns:

id - an auto-incrementinginteger which is the primary key, size 11

first_name - a varchar of maximum length 255, cannot be null

last_name - a varchar of maximum length 255, cannot be null

dob - a date cannot be null

date_joined - a date cannot be null

The combination of the first_nameand last_name must be unique in this table

Create a table called 'project' with the following columns:

id - an auto-incrementinginteger which is the primary key, size 11

cid - an integer which is a foreign key reference to the client table

name - a varchar of maximum length 255, cannot be null

notes - a text column

The project name must be unique in this table

Create a table called 'works_on' representing a many-to-many relationship between employees and projects, with the following properties:

eid - an integer which is a foreign key reference to the employee table

pid - an integer which is a foreign key reference to the project table

start_date - a date, not null

The primary key is a combination of eid and pid

Explanation / Answer

create table client (id int auto_incremnt,first_name varchar(255) not null,last_name varchar(255) not null,dob date not null,unique(first_name,last_name)); create table employee (id int auto_incremnt,first_name varchar(255) not null,last_name varchar(255) not null,dob date not null,unique(first_name,last_name)); create table project (id int auto_incremnt,cid int,name varchar(255) not null,notes varchar(20),unique(name),foreign key(cid) references client(id)); create table works_on(eid int,pid int,start_date date,primary key(eid,pid),foreign key(eid) references employee(id),foreign key(pid) references project(id));

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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