Database Modeling & Design Fourth Edition- P32: Database technology has evolved rapidly in the three decades since the rise and eventual dominance of relational database systems. While many specialized database systems (object-oriented, spatial, multimedia, etc.) have found substantial user communities in the science and engineering fields, relational systems remain the dominant database technology for business enterprises. | 142 CHAPTER 7 An Example of Logical Database Design Figure Conceptual data model diagram for UML Table Results of the Analysis of the Conceptual Data Model ER Construct FDs Customer many Job one cust-no - job-title Order many Customer one order-no - cust-no Salesperson many Department one sales-id - dept-no Item many Department one item-no - dept-no Order many Item many Salesperson one order-no item-no- sales-id Order many Department many Salesperson one order-no dept-no- sales-id Logical Design 143 create table customer cust_no char 6 job_title varchar 256 primary key cust_no foreign key job_title references job on delete set null on update cascade create table job job_no char 6 job_title varchar 256 primary key job_no create table order order_no char 9 cust_no char 6 not null primary key order_no foreign key cust_no references customer on delete set null on update cascade create table salesperson sales_id char 10 sales_name varchar 256 dept_no char 2 primary key sales_id foreign key dept_no references department on delete set null on update cascade create table department dept_no char 2 dept_name varchar 256 manager_name varchar 256 primary key dept_no create table item item_no char 6 dept_no char 2 primary key item_no foreign key dept_no references department on delete set null on update cascade 144 CHAPTER 7 An Example of Logical Database Design create table order_item_sales order_no char 9 item_no char 6 sales_id varchar 256 not null primary key order_no item_no foreign key order_no references order on delete cascade on update cascade foreign key item_no references item on delete cascade on update cascade foreign key sales_id references salesperson on delete cascade on update cascade create table order_dept_sales order_no char 9 dept_no char 2 sales_id varchar 256 not null primary key order_no dept_no foreign key order_no references order on delete cascade on update cascade foreign key dept_no references department on delete cascade on update .
