Question
USE library; INSERT INTO staff VALUES( 10001,'Stuart', 'Anne', '206-527-0010', 'Anne.Stuart@libray.com'); INSERT INTO staff VALUES( 10002,'Stuart', 'George', '206-527-0011', 'George.Stuart@library.com'); INSERT INTO staff VALUES( 10004,'Stuart', 'Mary', '206-527-0012',
USE library;
INSERT INTO staff VALUES(
10001,'Stuart', 'Anne', '206-527-0010', 'Anne.Stuart@libray.com');
INSERT INTO staff VALUES(
10002,'Stuart', 'George', '206-527-0011', 'George.Stuart@library.com');
INSERT INTO staff VALUES(
10004,'Stuart', 'Mary', '206-527-0012', 'Mary.Stuart@library.com');
INSERT INTO staff VALUES(
10005,'Orange', 'William', '206-527-0013', 'William.Orange@library.com');
INSERT INTO staff VALUES(
10006,'Matt', 'John', '206-527-0014', 'John.Griffith@library.com');
INSERT INTO staff VALUES(
10007,'Stuart', 'Jack', '206-527-0012', 'Jack.Stuart@library.com');
INSERT INTO staff VALUES(
10008,'Orange', 'Chris', '206-527-0013', 'Chris.Orange@library.com');
INSERT INTO staff VALUES(
10009,'Alexander', 'John', '206-527-0014', 'John.Alexanderh@library.com');
INSERT INTO staff VALUES(
10010,'William', 'Bradley', '206-527-0013', 'Bradley.William@library.com');
INSERT INTO staff VALUES(
10011,'Neil', ' Simon', '206-527-0014', ' Simon.Neilh@library.com');
INSERT INTO computer VALUES(
9871234, 'HP', 'Pavilion 500-210qe', 'Intel i5-4530', 3.00,
'6.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
9871245, 'HP', 'Pavilion 500-210qe', 'Intel i5-4530', 3.00,
'6.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
9871256, 'HP', 'Pavilion 500-210qe', 'Intel i5-4530', 3.00,
'6.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
9871267, 'HP', 'Pavilion 500-210qe', 'Intel i5-4530', 3.00,
'6.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
9871278, 'HP', 'Pavilion 500-210qe', 'Intel i5-4530', 3.00,
'6.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
9871289, 'HP', 'Pavilion 500-210qe', 'Intel i5-4530', 3.00,
'6.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
6541001, 'Dell', 'OptiPlex 9020', 'Intel i7-4770', 3.40,
'8.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
6541002, 'Dell', 'OptiPlex 9020', 'Intel i7-4770', 3.40,
'8.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
6541003, 'Dell', 'OptiPlex 9020', 'Intel i7-4770', 3.40,
'8.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
6541004, 'Dell', 'OptiPlex 9020', 'Intel i7-4770', 3.40,
'8.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
6541005, 'Dell', 'OptiPlex 9020', 'Intel i7-4770', 3.40,
'8.0 GBytes', '1.0 TBytes');
INSERT INTO computer VALUES(
6541006, 'Dell', 'OptiPlex 9020', 'Intel i7-4770', 3.40,
'8.0 GBytes', '1.0 TBytes');
INSERT INTO computer_staff(serial_number, staff_id, date_assigned)
VALUES(9871256, 10006, '2015-09-14');
INSERT INTO computer_staff (serial_number, staff_id, date_assigned)
VALUES(9871267, 10001, '2015-09-14');
INSERT INTO computer_staff(serial_number, staff_id, date_assigned)
VALUES(9871278, 10002, '2015-09-14');
INSERT INTO computer_staff(serial_number, staff_id, date_assigned)
VALUES(9871289, 10004, '2015-09-21');
INSERT INTO computer_staff(serial_number, staff_id, date_assigned)
VALUES(6541001, 10005, '2015-10-14');
INSERT INTO computer_staff (serial_number, staff_id, date_assigned)
VALUES(6541002, 10007, '2015-10-14');
INSERT INTO computer_staff(serial_number, staff_id, date_assigned)
VALUES(6541003, 10008, '2015-10-21');
INSERT INTO computer_staff (serial_number, staff_id, date_assigned)
VALUES(6541004, 10009, '2015-10-14');
INSERT INTO computer_staff(serial_number, staff_id, date_assigned)
VALUES(9871234, 10011, '2015-11-14');
INSERT INTO computer_staff (serial_number, staff_id, date_assigned)
VALUES(9871245, 10010, '2015-11-14');
INSERT INTO branch_staff (branch_id, staff_id, contact) VALUES(91234, 10001, '510-714-8321');
INSERT INTO branch_staff (branch_id, staff_id, contact) VALUES(92345 , 10010, '510-987-0000 ');
INSERT INTO branch_staff (branch_id, staff_id, contact) VALUES(93456 , 10009, '510-555-9743');
INSERT INTO branch_staff (branch_id, staff_id, contact) VALUES(94567 , 10008, '510-666-9743 ');
INSERT INTO branch_staff (branch_id, staff_id, contact) VALUES(94567, 10007, '510-645-9743 ');
INSERT INTO branch_staff (branch_id, staff_id, contact) VALUES(95678 , 10010, '8510-111-9743 ');
INSERT INTO branch_staff (branch_id, staff_id, contact) VALUES(96789 , 10011, '510-999-9743 ');
Objectives The purpose of the assignment is to understand how to create tables and design integrity constraints. In thi assignment, you will need to create a database, construct tables, insert data into the database, modif individual columns of a table, and execute queries. The assignment reinforces the following objectives: learn how to create table structures learn how to insert database data learn how to modify and delete data from a database learn how to query a database Read the directions carefully. If a query says it uses four SQL statements, then you must use exactly fou SQL statements for that query or you will receive no points. At various points, the query instructs you t ineselet satemens to dil table daher table displays in your final szript Additional files create schema library database create_tables_library_database insert data library_database.sql The library has decided to keep track of the library staff members and computers used by the staff. To complete this task, four new tables will be added to the database. staff (staff_id, fname, Iname, phone, email) computer (serial_number, make, model, processor_type, speed, main_memory, disk_size) computer_staff (serial_number, staff_id, date_assigned) branch_staff ( branch_id, contact, staff_id) Staff records are never deleted from the database. A computer's serial number never changes, so there is no need to cascade updates. When a computer is at the end of its life, the record in the computer table for that specific computer as well as all associated records in computer_staff table are deleted from the database
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started