![]() Advanced physical database design is often highly dependant on data density and data access patterns. Developing advanced physical design features, such as multidimensional clustering or materialized views, is far beyond the capabilities of the logical design tools we are discussing. However, the advanced features of the database server are often not supported-where they are supported, the CASE tool is usually behind by two or three software releases. These tools help create basic database objects, such as tables and, in some cases, indexes. It is worth noting that with all the CASE tools we discuss here, the conversion of the logical design to the physical design is quite rudimentary. The CASE tools will automatically generate the required scripts, including the DDL specification to create the actual database, and will provide you with an option to apply those changes to an actual database, as follows:Ĭreate table job (job_title varchar(256),įoreign key (cust_no) references customerĬreate table salesperson (sales_name varchar(256),įoreign key (dept_no) references departmentĬreate table department (dept_no char(2),Ĭreate table order_item_sales (order_no char(9),įoreign key (sales_name) references salespersonĬreate table order_dept_sales (order_no char(9),įoreign key (order_no) references order on delete cascade on update cascade, If we want to have an employee in multiple Stores, we could change the keys on JobAssignments, thus. ![]() Let's change the specs a bit and allow employees to work at more than one store. The updates and deletes will cascade and clean up the job assignments. You will need to have a stored procedure that inserts into both tables as a single transaction. The good optimizers extract things like that as predicates and use them, which is why we want Declarative Referential Integrity (DRI) instead of triggers and application side logic. Any unassigned employee will make the Personnel table bigger than the JobAssignments table, and an employee in JobAssignments must have a match in personnel. But the PK-FK requirement provides that for you. This is a surprise to people at first because they expect to see a JOIN to do the one-to-one mapping between personnel and job assignments. ![]() = (SELECT COUNT(emp_id) FROM Personnel)) CREATE ASSERTION Everyone_assigned_one_storeĬHECK ((SELECT COUNT(emp_id) FROM JobAssignments)
0 Comments
Leave a Reply. |