Undergrads and graduate students Do Exercises 2.4.7, 2.4.8 (undergrad extra cred
ID: 3757449 • Letter: U
Question
Undergrads and graduate students
Do Exercises 2.4.7, 2.4.8 (undergrad extra credit), 2.5.1(a,b,c) (hardcopy).
MySQL exercise (dropbox): Add the following constraints to your database schema from Homework2, exercises 2.3.1 and 2.4.1:
The model attribute forms a primary key for Product, PC, Laptop, and Printer. (Four key constraints)
The type of a product cannot be null.
Every model that appears in PC, Laptop, or Printer must also appear as a model in Product. (Three referential integrity/foreign key constraints).
Constrain the type of a product to be either 'pc', 'laptop', 'printer', 'smartphone', or 'tablet'. (One domain/check constraint)
You should include these constraints with the CREATE TABLE commands and recreate the database from scratch, as you did in Homework 2. (You could add the constraints directly--without disturbing data--with ALTER TABLE commands, described in class, but then your file cannot be tested from scratch by the TA.)
Once you are satisfied that you have the correct commands, put them all in a single file hw3.sql along with the data insertion commands from HW2. Then try the following commands in MySQL (assuming the original data was entered exactly as in Figures 2.20 and 2.21):
insert into Product(maker, model) values('I', 1014);
insert into Product values('J', 4001, 'toaster');
insert into Laptop values (2001, 3.00, 1024, 480, 15.6, 1995);
delete from Product where model=1001;
select * from Printer;
delete from Product where model=3001;
select * from Printer;
rollback;
Explanation / Answer
In order to create your database from scratch, I need the table structures and the columns present in each table.
For time being, what i am doing is I am creating the table with basic columns and the contraints. You can modify these CREATE TABLE queries to include other columns. In case, you have difficulty, feel free to reach out to me.
QUERIES
===============
CREATE TABLE Product (
model INTEGER UNIQUE NOT NULL,
make VARCHAR(10),
type VARCHAR(50),
PRIMARY KEY (model),
CHECK (type in (pc', 'laptop', 'printer', 'smartphone', 'tablet'))
);
CREATE TABLE Laptop (
id INTEGER UNIQUE NOT NULL,
laptopModel INTEGER NOT NULL,
......your other columns goes here.....,
PRIMARY KEY (id),
FOREIGN KEY (laptopModel) REFERENCES Product(model)
);
CREATE TABLE PC (
id INTEGER UNIQUE NOT NULL,
pcModel INTEGER NOT NULL,
......your other columns goes here.....,
PRIMARY KEY (id),
FOREIGN KEY (pcModel) REFERENCES Product(model)
);
CREATE TABLE Printer (
id INTEGER UNIQUE NOT NULL,
printerModel INTEGER NOT NULL,
......your other columns goes here.....,
PRIMARY KEY (id),
FOREIGN KEY (printerModel) REFERENCES Product(model)
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.