F
Sample Designs
I’ve provided these sample designs to serve as ideas for databases you may want or need to create. I emphasize the word ideas because five people can look at the same design and come up with five distinct variations based on their needs, backgrounds, and personal points of view. Remember that there is no right or wrong way to design a given database, but you do have to ensure that the tables, fields, relationships, and views all conform to the guidelines you’ve learned from this book.
I intentionally omitted all but the primary and foreign key fields from each table because I did not want to greatly influence you in any way as to how the tables should be populated. I also omitted a majority of the relationship characteristics for the same reason.
Should you see a design that you might be able to use, run it through the entire database-design process and treat it like an existing database. At the end of the process, you should have a database that suits your needs.
The sample design of the entertainment agency database depicts the database objects and their relationship with each other. In total, the design has five data tables for classes such as customers, engagements, agents, entertainers, and members; three linking tables for musical preferences, entertainer members, and entertainer styles; and one validation table for musical styles. The relationship existing between the entities within each table is as follows. The primary key entity that is the customer Id of the customer data table is in a one-to-many relationship with the foreign key entity that is the customer ID of the engagements data table and with the composite primary/foreign key entity that is the customer ID of musical preference linking table. The style ID (composite primary/foreign key) of the musical preferences table is in a many-to-one relationship with the style ID (Primary key) of the musical styles validation table, which in turn is in a one-to-many relationship with the style ID (composite primary/foreign key) of entertainer styles linking table. The customer ID (composite primary/foreign key) within this linking table is in a many-to-one relationship with the entertainer ID (Primary key) of the entertainer data table, which in turn is in a one-to-many relationship with the entertainer ID (Foreign key) of the engagements data table and with the entertainer ID (composite primary/foreign key) of the entertainer member linking table. The agent ID (Foreign key) in the engagement data table is in a many-to-one relationship with the agent ID (Primary key) of the agent data table. The member ID (composite primary/foreign key) is in a many-to-one relationship with the member ID (Primary key) of the member data table.
A sample design of the school database depicts the database objects and their relationship with each other. In total, the design has six data tables for objects such as staff, subjects, students, classes, classrooms, and buildings; four linking tables for faculty categories, faculty classes, faculty subjects, and student schedules; and three validation tables for categories, departments, and student class status. The relationship existing between the entities within each table is as follows. The staff ID (Primary Key) of the faculty subset table is in a one-to-one relationship with the staff ID (Primary Key) of the staff data table, one-to-many relationship with the staff ID (composite primary/foreign key) of the faculty categories data table, one-to-many relationship with staff ID (Composite primary or foreign key) of faculty classes linking table, and one-to-many relationship with the staff ID (composite primary or foreign key) of faculty subjects linking table. The category ID (composite primary or foreign key) of the faculty categories linking table is in a many-to-one relationship with the category ID (primary key) of the categories validation table, which in turn is in a one-to-many relationship with the category ID (foreign key) of the subject data table. The department ID (foreign key) of the categories linking table is in a many-to-one relationship with the department ID (foreign key) of the departments linking table. The subject ID (composite primary or foreign key) of the faculty subjects linking table is in a many-to-one relationship with the subject ID (foreign key) of the subjects data table, which in turn is in a one-to-many relationship with the subject ID (foreign key) of the classes data table. The class ID (composite primary or foreign key) of the faculty classes linking table is in a many-to-one relationship with the class ID (primary key) of the classes data table, which in turn is in a one-to-many relationship with the class ID (composite primary or foreign key) of the student schedules linking table. The classroom ID (foreign key) of the classes data table is in a many-to-one relationship with the classroom ID (primary key) of the classrooms data table and the building code (foreign key) of the classrooms data table is in a many-to-one relationship with the building code (primary key) of the buildings data table. The student ID (primary key) of the student data table is in a one-to-many relationship with the student ID (composite primary or foreign key) of the student schedule linking table and the class status (foreign key) within this linking table is in a many-to-one relationship with the class status (Primary key) of the student class status validation table.
A sample design of the sales order database depicts the database objects and their relationship with each other. In total, the design has five data tables for classes such as customers, employees, orders, products, and vendors; two linking tables for order details and product vendors; and one validation table for categories. The relationship existing between the entities within each table is as follows. The customer ID (primary key) of the customer data table is in a one-to-many relationship with the customer ID (foreign key) of the orders data table. The employee ID (primary key) of the employee data table is in a one-to-many relationship with the employee ID (foreign key) of the orders data table and the order ID (primary key) of the orders data table is in a one-to-many relationship with the order ID (composite primary or foreign key) of the order details linking table whereas, the product number (composite primary or foreign key) in this linking table is in a one-to-many relationship with the product number (primary key) of the products data table. This in turn, is in a one-to-many relationship with the product number (composite primary or foreign key) of the product vendors linking table and the vendor ID (composite primary or foreign key) of this linking table is in a many-to-one relationship with the vendor ID (primary key) of the vendors data table. The category ID (primary key) in the categories validation table is in a one-to-many relationship with the category ID (foreign key) of the products data table.
A sample design of the office inventory database depicts the database objects and their relationship with each other. In total, the design has three data tables for objects such as items, containers, and storage locations; three subset tables for software, office furniture, and office equipment; and one linking table for container items. The relationship existing between the entities within each table is as follows. The item ID (primary key) of the software subset table is in a one-to-one relationship with the item ID (primary key) of the office equipment subset table. The item ID (primary key) of the office furniture subset table is in a one-to-one relationship with the item ID (primary key) of the items data table, which in turn is in a zero or one relationship with the item ID (composite primary or foreign key) of the container items linking table. The container ID (composite primary or foreign key) in this linking table is in a many-to-one relationship with the container ID (primary key) of the containers data table and the storage location ID (foreign key) of this data table is in a many-to-one relationship with the storage location ID (primary key) of the storage locations data table.
A sample design of the bowling league database depicts the database objects and their relationship with each other. In total, the design has six data tables for objects such as tournaments, teams, bowlers, bowler scores, and two matches - one with primary and foreign key entities and the other with composite primary or foreign key entities; and one linking table for team members. The relationship existing between the entities within each table is as follows. The tournament ID (primary key) of the tournaments data table is in a one-to-many relationship with the tournament ID (foreign key) of the first matches data table. The odd-lane team ID (foreign key) and even-lane team ID (foreign key) in this first matches data table are in a many-to-one relationship with the team ID (primary key) in the team data table, which in turn is in a one-to-many relationship with the team ID (composite primary or foreign key) in the team members linking table. The bowler ID (composite primary or foreign key) in this linking table is in a many-to-one relationship with the bowler ID (Primary key) of the bowlers data table, which in turn is in a one-to-many relationship with the bowler ID (composite primary or foreign key) in the bowler scores data table. The match ID (composite primary or foreign key) and game number (composite primary or foreign key) in the second matches data table are in a one-to-many relationship with the match ID (composite primary or foreign key) and game number (composite primary or foreign key) of the bowler scores data table. The team ID (primary key) of the team data table is in a one-to-many relationship and the odd-lane team ID (foreign key) and the even-lane team ID (foreign key) are in a many-to-many relationship with the winning team ID (foreign key) of the matches data table.
A sample design of the car rental database depicts the database objects and their relationship with each other. In total, the design has five data tables for objects such as employees, customers, vehicles, locations, and maintenance work orders; one linking table for rentals; and one validation tables for maintenance types. The relationship existing between the entities within each table is as follows. The employee ID (primary key) and the supervisor ID (foreign key) within the employee data table are in a one-to-many relationship whereas, the employee ID is in a one-to-many relationship with the employee ID (foreign key) of the rentals linking table. The customer ID (primary key) of the customer data table is in a one-to-many relationship with the customer ID (foreign key) of the rentals linking table. The license number (foreign key) of the rentals linking table is in a many-to-one relationship with the license number (primary key) of the vehicle data table and this license number (primary key) is in a one-to-many relationship with the license number (foreign key) of the maintenance work orders. The maintenance type ID (foreign key) of the maintenance work orders is in a many-to-one relationship with the maintenance type ID (primary key) of the maintenance types validation table. The location ID (foreign key) of the rentals linking table is in a many-to-one relationship with the location ID (primary key) of the locations data table.
..................Content has been hidden....................
You can't read the all page of ebook, please click
here login for view all page.