Database Modeling & Design Fourth Edition- P21: 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. | Transformation Rules and SQL Constructs 87 Every employee works in exactly one department and each department has at least one employee. create table department dept_no integer dept_name char 20 primary key dept_no create table employee emp_id char 10 emp_name char 20 dept_no integer not null primary key emp_id foreign key dept_no references department on delete set default on update cascade d One-to-many both entities mandatory Each department publishes one or more reports. A given report may not necessarily be published by a department. create table department dept_no integer dept_name char 20 primary key dept_no create table report report_no integer dept_no integer primary key report_no foreign key dept_no references department on delete set null on update cascade e One-to-many one entity optional one mandatory Every professional association could have none one or many engineer members. Each engineer could be a member of none one or many professional associations. create table engineer emp_id char 10 primary key emp_id create table prof_assoc assoc_name varchar 256 primary key assoc_name create table belongs_to emp_id char 10 assoc_name varhar 256 primary key emp_id assoc_name foreign key emp_id references engineer on delete cascade on update cascade foreign key assoc_name references prof_assoc on delete cascade on update cascade f Many-to-many both entities optional Figure continued 88 CHAPTER 5 Transforming the Conceptual Data Model to SQL Every report has one abbreviation and every abbreviation represents exactly one report. create table report report_no integer report_name varchar 256 primary key report_no create table abbreviation abbr_no char 6 report_no integer not null unique primary key abbr_no foreign key report_no references report on delete cascade on update cascade a one-to-one both entities mandatory Every department must have a manager but an employee can be a manager of at most one department. create table department dept_no integer .
