1 / 23

Database Design

Database Design. CIS 218. Three Stages of Database Development. Requirements Design Implementation. The Design Stage. Identify Entities Identify Attributes Choose Primary Keys Normalization Identify Relationships. Entity. A Person, Place, Thing or Transaction

clay
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 CIS 218

  2. Three Stages ofDatabase Development • Requirements • Design • Implementation

  3. The Design Stage • Identify Entities • Identify Attributes • Choose Primary Keys • Normalization • Identify Relationships

  4. Entity • A Person, Place, Thing or Transaction • Something the user wants to track

  5. Entities • At HCC, what are the things we might want to track? • An entity is represented as a table

  6. Subject Author Title ??? Number of Pages Attributes • An Attribute is a property that describes an entity • A Book has a(n)…

  7. Attributes • What are the attributes of a Car?

  8. Attributes • Attributes describe an entity’s characteristics • Employee(ssn, first, last, jobTitle) • Student(sid, name, gpa, email, phone) • Flight(flightNum, date, time, origin, destination) • Book(isbn, title, author, genre) • Represented as columns in a table entity attributes

  9. Practice • Identify three entities in the list below. • Identify three attributes that belong to each entity. Breed Size Shirt CPU Speed Manufacturer Serial Number Style Weight Color Animal Gender Computer

  10. Break up Complex Attributes

  11. What are the entitites? What are the attributes of each entity?

  12. What are the entitites? What are the attributes of each entity?

  13. What are the entitites? What are the attributes of each entity?

  14. Primary Key • A unique identifier • No two rows in a table may be identical

  15. Primary Keys Student sid ssn name phone email • Criteria • Short • Numeric • Stable • Not private • Surrogate Key • If no unique identifier exists, a surrogate key is used as the primary key, usually an AutoNumber • Usually hidden in forms, reports, and queries • Composite Key • A primary key consisting of more than one column

  16. Practice • What would make the best primary key for each entity? • Employee(ssn, first, last, jobTitle) • Flight(flightNum, date, time, origin, destination) • Member(firstName, lastName, dateJoined, status) • Grade(sid, yrq, itemNum, grade)

  17. Be Normal • A table that is “well-formed” is normalized • The Golden Rule of Being Normal If attribute X can have more than one attribute Y, then they don’t belong in the same table!

  18. A Course can have more than one Book! Course CourseID CourseName Textbook1 Textbook2 Course CourseID CourseName Textbook ISBN Title Course CourseID CourseName Textbook Title

  19. An Advisor can have more than one Student! Student sid name gender advisorName extension Student sid name gender Advisor name extension Student sid name gender Advisor advisorID name extension

  20. A Pet can have more than one Treatment! Pet petID name treatment price Treatment treatmentID treatmentName price Pet petID name Treatment treatmentName price Pet petID name

  21. A Pet can have more than one Breed! Pet petID name breed Breed breedID breedName Pet petID name breed Pet petID name breed Breed breedName

  22. A Breed can have more than one Pet! Pet petID name breed minWeight maxWeight avgLifeExpectancy Breed breed min_weight max_weight avg_life_expectancy Breed breedID breed minWeight maxWeight avgLifeExpectancy Pet petID name Pet petID name

  23. Denormalization • Complexity vs. modification problems • Normalizing relations may significantly increase the complexity of the data structure • Denormalized relations may be preferred • CUSTOMER and ZIP • It is inefficient to read from two different tables to get a customer’s address • Modification problems are infrequent, because zip codes rarely change • Deletion problems are not usually an issue

More Related