Exercise 1 Discuss the main approaches for managing the design of a database system that has...

 

 

 

 

Transcribed Image Text:

Exercise 1 Discuss the main approaches for managing the design of a database system that has multiple user views. Exercise 2 Compare and contrast the three phases of database design. Exercise 3 Create an ER diagram (entities, relationships, multiplicities) for each of the following descriptions: (a) Each company operates four departments, and each department belongs to one company. (b) Each department in part (a) employs one or more employees, and each employee works for one department. (c) Each of the employees in part (b) may or may not have one or more dependants, and each dependant belongs to one employee. (d) Each employee in part (c) may or may not have an employment history. (e) Represent all the ER diagrams described in (a), (b), (c), and (d) as a single ER diagram. Perilous Printing Database Description Perilous Printing is a small printing company which does work for book publishers (tracked in the publishers table). Perilous Printing jobs consist of printing books or parts of books. These jobs are recorded in the bookjobs table, which contains a foreign key to a customer (cust_id). A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders (POS) kept in the pos table (with the foreign key job_id back to the bookjobs table). Each printing job may have several POs assigned to it. Likewise, one PO may contain many PO items which are recorded in a separate po_items table. The one-to-many relationship between pos and po_items is implemented by the composite foreign key (job_id, po_id) in the po_items table. The materials which appear in po_items (via foreign key item_id) are tracked in the items table, which records the material description, the quantity on-hand in the warehouse, and the price. The many-to-many (*:*) relationship between pos and items is decomposed into two one-to-many (1:*) relationships by means of the intersection relation po_items. Perilous Printing Relation Structures publishers (cust id, name, city, phone, creditcode) bookjobs (job id, cust_id, job_date, descr, jobtype) pos (job id, po id, po_date, vendor_id) items (item id, descr, on_hand, price) po_items (job id, po id, item id, quantity) Exercise 4 Reverse engineer an ER model from the above description above. Include entities, attributes, relationships, and multiplicities. Note that: foreign keys should NOT be represented in the entities as attributes, but are information about the required relationships the relation po_items is actually the realisation of a many-to-many relationship between pos and items and has the attribute quantity DROP TABLE publishers; DROP TABLE po_items; DROP TABLE bookjobs; DROP TABLE items; DROP TABLE pos; CREATE TABLE pos ( job_id po_id po_date vendor_id name city phone creditcode ); CREATE TABLE cust_id CHAR(3) NOT NULL, CHAR(3) NOT NULL, DATE, CHAR (3) on_hand price ); publishers ( CHAR (3) CHAR(10), CHAR(10), CHAR (8), CHAR (1) ); CREATE TABLE bookjobs ( job_id cust_id job_date descr jobtype ); CHAR(3) CHAR(3), DATE, CHAR(10), CHAR (1) CREATE TABLE po_items ( job_id po_id item_id quantity ); CHAR (3) NOT NULL, CREATE TABLE items ( item_id descr CHAR(3) NOT NULL, SMALLINT NOT NULL, CHAR (3) NOT NULL, NOT NULL, CHAR(10), CHAR (3) NOT NULL, SMALLINT, DECIMAL (5,2) insert into publishers values ('A01', 'ART BOOKS', 'SYDNEY', '555-1234', 'N'); insert into publishers values ('B02', 'BIBLECO', NULL, '555-2468', 'C'); CABLE-EX', 'BRISBANE', '555-3690', 'N'); 'DIABLO CO', 'MELBOURNE', NULL, 'D'); insert into publishers values ('C03', insert into publishers values ('D04', insert into publishers values ('E05', 'EASYPRINT' 'PERTH' 555-5050', 'C'); insert into publishers values ('F06', 'FOX-PAW', 'HOBART', '555-6789', 'C' ); insert into publishers values ('G07', 'GOLD PRESS', 'ADELAIDE', '555-7777', 'N'); insert into publishers values ('H08', 'HELP BOOKS', 'DARWIN', NULL, 'C'); insert into bookjobs values ('001' 'E05' insert into bookjobs values ('002', 'E05' insert into bookjobs values ('003', 'E05' insert into bookjobs values ('004', 'A01' insert into bookjobs values ('005' 'A01 insert into bookjobs values ('006', 'D04', I I I 'ABC'); 1 '1990-05-20 '1990-03-15' 'XYZ'); '1990-01-05' '1990-01-01', '1990-01-15', 'SOS '); insert into pos values ('002', AAA insert into pos values ('002', 'BBB' insert into pos values ('004', CCC insert into pos values ('004' 'DDD insert into pos values ('005' 'EEE insert into pos values ('005', FFF insert into pos values ('006', 'GGG' '1988-07-15', 'XYZ'); 'ABC'); 'SOS'); '1989-12-01', 'ABC'); 1 '1990-04-04' 'TEXT BOOKS 'R'); '1990-03-03' 'BUS REPORT', 'N'); 1989-12-25', COMMERCIAL', 'N'); 1990-01-01 'PAMPHLETS', 'R'); '1989-11-23', 'GOVT', 'N'); '1988-07-04', 'CAMPAIGN', 'H'); I I insert into items values ('P9', '9KG PAPER', 300, 25.25); insert into items values ('P12', '12KG PAPER', 700, 49.99); insert into items values ('P18', '18KG PAPER', 100, 100.00); insert into items values ('IRN', 'INK-RESIN', 3, 500.00); insert into items values ('IWS', INK-WRSOL', 5, 350.00); insert into items values ('CBD', 'CARDBOARD', 47, 15.00); 'CCC 'IRN' 4); 'P18', 100); 'P9', 50); 'IWS insert into po_items values ('004', CCC', 'P9', 150); insert into po_items values ('004', insert into po_items values ('004', 'DDD' insert into po_items values ('002', 'AAA' insert into po_items values (002' insert into po_items values ('002' insert into po_items values ('002', 'BBB' insert into po_items values ('006', 'GGG', 'AAA 2); 'AAA' 'CBD' 17); 'CBD', 17); 'IRN', 2); Exercise 1 Discuss the main approaches for managing the design of a database system that has multiple user views. Exercise 2 Compare and contrast the three phases of database design. Exercise 3 Create an ER diagram (entities, relationships, multiplicities) for each of the following descriptions: (a) Each company operates four departments, and each department belongs to one company. (b) Each department in part (a) employs one or more employees, and each employee works for one department. (c) Each of the employees in part (b) may or may not have one or more dependants, and each dependant belongs to one employee. (d) Each employee in part (c) may or may not have an employment history. (e) Represent all the ER diagrams described in (a), (b), (c), and (d) as a single ER diagram. Perilous Printing Database Description Perilous Printing is a small printing company which does work for book publishers (tracked in the publishers table). Perilous Printing jobs consist of printing books or parts of books. These jobs are recorded in the bookjobs table, which contains a foreign key to a customer (cust_id). A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders (POS) kept in the pos table (with the foreign key job_id back to the bookjobs table). Each printing job may have several POs assigned to it. Likewise, one PO may contain many PO items which are recorded in a separate po_items table. The one-to-many relationship between pos and po_items is implemented by the composite foreign key (job_id, po_id) in the po_items table. The materials which appear in po_items (via foreign key item_id) are tracked in the items table, which records the material description, the quantity on-hand in the warehouse, and the price. The many-to-many (*:*) relationship between pos and items is decomposed into two one-to-many (1:*) relationships by means of the intersection relation po_items. Perilous Printing Relation Structures publishers (cust id, name, city, phone, creditcode) bookjobs (job id, cust_id, job_date, descr, jobtype) pos (job id, po id, po_date, vendor_id) items (item id, descr, on_hand, price) po_items (job id, po id, item id, quantity) Exercise 4 Reverse engineer an ER model from the above description above. Include entities, attributes, relationships, and multiplicities. Note that: foreign keys should NOT be represented in the entities as attributes, but are information about the required relationships the relation po_items is actually the realisation of a many-to-many relationship between pos and items and has the attribute quantity DROP TABLE publishers; DROP TABLE po_items; DROP TABLE bookjobs; DROP TABLE items; DROP TABLE pos; CREATE TABLE pos ( job_id po_id po_date vendor_id name city phone creditcode ); CREATE TABLE cust_id CHAR(3) NOT NULL, CHAR(3) NOT NULL, DATE, CHAR (3) on_hand price ); publishers ( CHAR (3) CHAR(10), CHAR(10), CHAR (8), CHAR (1) ); CREATE TABLE bookjobs ( job_id cust_id job_date descr jobtype ); CHAR(3) CHAR(3), DATE, CHAR(10), CHAR (1) CREATE TABLE po_items ( job_id po_id item_id quantity ); CHAR (3) NOT NULL, CREATE TABLE items ( item_id descr CHAR(3) NOT NULL, SMALLINT NOT NULL, CHAR (3) NOT NULL, NOT NULL, CHAR(10), CHAR (3) NOT NULL, SMALLINT, DECIMAL (5,2) insert into publishers values ('A01', 'ART BOOKS', 'SYDNEY', '555-1234', 'N'); insert into publishers values ('B02', 'BIBLECO', NULL, '555-2468', 'C'); CABLE-EX', 'BRISBANE', '555-3690', 'N'); 'DIABLO CO', 'MELBOURNE', NULL, 'D'); insert into publishers values ('C03', insert into publishers values ('D04', insert into publishers values ('E05', 'EASYPRINT' 'PERTH' 555-5050', 'C'); insert into publishers values ('F06', 'FOX-PAW', 'HOBART', '555-6789', 'C' ); insert into publishers values ('G07', 'GOLD PRESS', 'ADELAIDE', '555-7777', 'N'); insert into publishers values ('H08', 'HELP BOOKS', 'DARWIN', NULL, 'C'); insert into bookjobs values ('001' 'E05' insert into bookjobs values ('002', 'E05' insert into bookjobs values ('003', 'E05' insert into bookjobs values ('004', 'A01' insert into bookjobs values ('005' 'A01 insert into bookjobs values ('006', 'D04', I I I 'ABC'); 1 '1990-05-20 '1990-03-15' 'XYZ'); '1990-01-05' '1990-01-01', '1990-01-15', 'SOS '); insert into pos values ('002', AAA insert into pos values ('002', 'BBB' insert into pos values ('004', CCC insert into pos values ('004' 'DDD insert into pos values ('005' 'EEE insert into pos values ('005', FFF insert into pos values ('006', 'GGG' '1988-07-15', 'XYZ'); 'ABC'); 'SOS'); '1989-12-01', 'ABC'); 1 '1990-04-04' 'TEXT BOOKS 'R'); '1990-03-03' 'BUS REPORT', 'N'); 1989-12-25', COMMERCIAL', 'N'); 1990-01-01 'PAMPHLETS', 'R'); '1989-11-23', 'GOVT', 'N'); '1988-07-04', 'CAMPAIGN', 'H'); I I insert into items values ('P9', '9KG PAPER', 300, 25.25); insert into items values ('P12', '12KG PAPER', 700, 49.99); insert into items values ('P18', '18KG PAPER', 100, 100.00); insert into items values ('IRN', 'INK-RESIN', 3, 500.00); insert into items values ('IWS', INK-WRSOL', 5, 350.00); insert into items values ('CBD', 'CARDBOARD', 47, 15.00); 'CCC 'IRN' 4); 'P18', 100); 'P9', 50); 'IWS insert into po_items values ('004', CCC', 'P9', 150); insert into po_items values ('004', insert into po_items values ('004', 'DDD' insert into po_items values ('002', 'AAA' insert into po_items values (002' insert into po_items values ('002' insert into po_items values ('002', 'BBB' insert into po_items values ('006', 'GGG', 'AAA 2); 'AAA' 'CBD' 17); 'CBD', 17); 'IRN', 2);