1 / 22

Database Design

Database Design. Ack: Amanda Gerdes. Overview. Learning the Language What’s A Database? How Do We Talk About Databases? Database Models “Flat file” Database Relational Database Relational Database Design Step One: Determine The Information You Need

althea
Download Presentation

Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design Ack: Amanda Gerdes

  2. Overview • Learning the Language • What’s A Database? • How Do We Talk About Databases? • Database Models • “Flat file” Database • Relational Database • Relational Database Design • Step One: Determine The Information You Need • Step Two: Assign Your Attributes To Entities • Step Three: Assign Primary Keys To Your Entities • Step Four: Determine The Relationships Between Your Entities • Step Five: Link Your Entities Together

  3. Learning the LanguageWhat’s A Database? Oooookay… that’s, uh… that’s just great. What the hell’s a schema? Or a DBMS? • Database: a collection of related records that is typically: • ... structured according to a schema • … managed through a Database Management System (DBMS) • Schema: the overall structure of the database which defines data represented in the database and the relationships between them • Database Management System (DBMS): a collection of software programs which aids in the storage, manipulation, reporting, management, and control of data that: • … is a “front end” for the database, hiding potentially complex data relationships from the users • … includes things like sales and order tracking systems and airline reservation systems • … is often just called a “database” Hey, great. That’s not confusing at all.

  4. Learning the LanguageHow Do We Talk About Databases? • Table: A set of data elements organized into horizontal rows (records) and vertical columns (fields). • Each database can have multiple tables! • A table has a specific number of columns but can have any number of rows. • Record: A row in the database representing a single set of related data. All records have the same structure (in this case id, name, and planet) and each element of a record is related. • Field: A column in the database representing a set of a type of data. In this case, the fields are id, name, and planet and each column provides a list of id numbers, names, and planets respectively. • We will often see data types associated with fields. For example, our id field expects numbers.

  5. Database ModelsThe Flat Model • Flat Model: A two-dimensional array of data elements, consisting of only data and delimiters • Delimiter: A sequence of one or more characters that specifies the boundary between fields and between records

  6. Database ModelsThe Flat Model (continued) Tab-Separated Record delimiter: Newline Field delimiter: Tab Comma-Separated “1”,”Luke”,”Tatooine” “2”,”Leia”,”Alderaan” “3”,”Han”,”Corellia” “4”,”Chewbacca”,”Kashyyyk” “5”,”Yoda”,”Coruscant” Record delimiter: Newline Field delimiter: Comma Other 1-Luke-Tatooine/2-Leia-Alderaan/3-Han-Corellia/4-Chewbacca-Kashyyyk/5-Yoda-Coruscant/ Record delimiter: / Field delimiter: -

  7. Database ModelsThe Flat Model (continued) • Limitations • The “flat file” database works when you’re collecting non-redundant data. Luke Skywalker only has one home planet; we’re not going to see him in our database twice. • But what if we want to know what planets he’s visited? Suddenly, things are less simple…

  8. Database ModelsThe Relational Model • Relational Model: A database consisting of multiple tables, each similar to a “flat file” table, which are linked together through common fields • Pieces of the Relational Database • Entity: A table in a relational database. • Attribute: A field in a relational database. • Key: An attribute used to identify, sort, or link records in a database. • Primary Key: A key that is a unique identifier for a single, specific record contained in a specified field. • Foreign Key: A key that acts as a “link” between different tables/entities.

  9. Product Information Manufacturer Information Database ModelsThe Relational Model (continued) • Relational Model: … continued … • Database Normalization: The process of restructuring a database to eliminate redundancy and organize data efficiently. • Third Normal Form (3NF): The most common method of structuring a database; method where any field that is not directly related to the key is moved to a separate table.

  10. Relational Database DesignOur Example • We’re part of a college advising team. Our job is to meet with students prior to the start of the semester, review their academic performance with them, go over pending degree requirements, and then counsel them about their best options for the coming semester.

  11. Relational Database DesignStep One: Determine The Information You Need • Make a list of the information we need.

  12. Info About Students Info About Classes Info About Professors Tables/Entities Fields/Attributes Relational Database DesignStep Two: Assign Your Attributes To Your Entities • Now that we know what information we need, our next job is to organize the data pieces (fields/attributes) into categories (tables/entities).

  13. Relational Database DesignStep Three: Assign Primary Keys To Your Entities • We now have three separate tables (entities) for our database. We now need a way to uniquely identify each row in each of the tables. • To determine a primary key, let’s do the following: • Determine if we already have a field that uniquely identifies each row. Each value in this field must be completely unique within the entire table. • If we do: This is called a natural keyand we can use it as our primary key. • If we don’t: We will have to add a field to our database to hold a unique identifier we can use as a primary key. We can either add a data field (e.g., a SSN acting as an ID number) or create what we call a surrogate key; this is just a “fake” value that has no real-world meaning to our row but will uniquely identify it (e.g., having the DBMS generate a random number or auto-increment number for every record)

  14. Professor ID 123-45-6789 234-56-7890 345-67-8901 456-78-9012 Relational Database DesignStep Three (Continued)

  15. Relational Database DesignStep Four: Determine The Relationships Between Your Entities • Now that we have our entities more or less in order, our next job is to figure out how they are related to each other. • One-to-One (1:1). One instance of Entity A relates to one instance of Entity B. • Example: Every employee gets his/her own office; each employee gets only one office, each office houses only one employee • One-to-Many (1:N). One instance of Entity A relates to many instances of Entity B. • Example: Employees are assigned to different regional centers; each employee works at only one regional center, each regional center houses multiple employees • Many-to-Many (M:N). Many instances of Entity A relate to many instances of Entity B. • Example: Employees are assigned to projects; one employee has many projects and each project has many employees assigned to it

  16. MANY MANY MANY ONE Many-To-Many One-To-Many Relational Database DesignStep Four: (continued) • Determine the relationship for our entities:

  17. Foreign Key Primary Key = Relational Database DesignStep Five: Link Your Entities Together • Now that we have our tables and we know how they are related to each other… we must link them together. • Linking Tables • Related tables are linked via a common field • The primary key in Entity A will appear as a foreign key in Entity B; we can then match Entity A’s primary key to Entity B’s foreign key for a match.

  18. Relational Database DesignStep Five: (continued) • Guidelines for Linking Tables • One-to-One: Either table can hold the foreign key. • One-to-Many: The “Many” table should hold the foreign key. • Many-to-Many: We will create a joint table that contains the primary keys for both tables.

  19. Relational Database DesignStep Five: (continued) • Linking Professors and Classes • Relationship: ONE Professor to MANY Classes • Keep in Mind: • Foreign key belongs in the MANY table • Foreign key in one table = Primary Key of the other table

  20. Relational Database DesignStep Five: (continued) • Linking Students and Classes • Relationship: MANY Students to MANY Classes • With a Many-to-Many relationship, we have to create a joint table! • A Joint Table should include: • A primary key • Foreign keys to each of the tables we’re linking • Any data fields that are specific to the joint relationship (e.g., a student’s grade depends on both the student and the class)

  21. Relational Database DesignStep Five: (continued)

  22. Relational Database DesignSummary View

More Related