Database Systems Overview: Spring 2017 Project Insights

overview of database systems n.w
1 / 33
Embed
Share

Learn about database systems in CPSC 315 Programming Studio's Spring 2017 Project. Explore the basics of creating and querying databases, understanding schemas, and more.

  • Database Systems
  • CPSC 315
  • Programming Studio
  • Spring 2017
  • Project

Uploaded on | 0 Views


Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.

E N D

Presentation Transcript


  1. Overview of Database Systems CPSC 315 Programming Studio Spring 2017 Project 1, Lecture 1

  2. Project Your first project (next week) will involve putting together a very basic database system There will be a few lectures to give you an overview of database systems This is nowhere close to what you would get in a full database course Slides adapted from Jennifer Welch (some of hers were from Jeffrey Ullman)

  3. Database Systems Systems designed to manage very large amounts of data, and to query that data to pull out useful information Often, key considerations include: Efficiency Reliability Ease of access (querying, distributed)

  4. Databases A critical part of most IT operations Accessing database information can be as common as accessing any variable/object stored in memory But, the process for accessing it is different This can be good in that it helps highlight the difference with data in memory.

  5. Creating a Database A database schema determines what will be represented in the database This should be tightly controlled by a database manager Specified through a data definition language

  6. Querying Databases Once database has been populated, users can query the data A data manipulation language controls how the user can specify queries, (and thus what types of queries are allowed) SQL is probably the most well-known

  7. Other Database Topics Real database courses include lots of other things that we ll be ignoring here More complete theory behind design Query optimization Efficient storage Processing Transactions grouped queries that provide atomic operations Scheduling, logging, recovery

  8. Entity-Relationship Model Way of expressing (in diagrammatic form) a database design Kinds of data and how they connect Easy first way to think about databases Later, relational model described Relational model is the foundation of most databases

  9. Entities and Attributes Entities are things Entity sets are collections of those things Attributes are properties of entity sets

  10. Entity Sets and Attributes Name Party Senator Bill State Text Name Years

  11. Relationships Connect two or more entity sets Name Contributed Party Organization Senator Lobbyist State Name Years Sponsored Wrote Bill Name Text

  12. Values of Relationships The value of an entity set is the entities it contains The value of a relationship is a list of currently related entities (one from each entity set) Senator Bill Smith Tax Bill Smith Defense Bill Jones Tax Bill

  13. Multi-Way Relationships E.g. Lobbyist lobbied Senator about Bill Name Party Organization Senator Lobbied Lobbyist State Name Years Bill Name Text

  14. Relationship Types Consider binary relationships (two entity groups in a relationship) One-to-one Each entity can have at most one in the other category e.g. entity groups: Baseball player, Team relationship: Team MVP A team can only have one MVP, and a player can only be MVP for one team.

  15. Relationship Types Consider binary relationships (two entity groups in a relationship) One-to-one Many-to-one Each entity of first set can go to at most one of the second set e.g. entity groups: Person, Town relationship: BornIn A person can is born in only one town, but a town can have many people born there

  16. Relationship Types Consider binary relationships (two entity groups in a relationship) One-to-one Many-to-one Many-to-many Any number from one set to the other e.g. Senators can sponsor many bills, and each bill can be sponsored by many Senators

  17. Diagrams of Relationships Arrow shows to one Lived In Person Town Born In Baseball Player MVP Team

  18. Attributes on Relationships Can be converted to multi-way diagrams Person Town Born In Hospital

  19. Attributes on Relationships Can be converted to multi-way diagrams Person Town Born In Hospitals Hospital

  20. Attributes on Relationships Note arrows Person Date Injured Hospitals Hospital

  21. Roles If multiple references to same entity set, label edges by roles Team Lead Programmer Students Team Tester

  22. Subclass Fewer entities, more properties Name Elected Official Party isa U.S. District Representative

  23. Subclass Entity in all subclasses Name Elected Official isa isa U.S. Republican Representative District Years in Party

  24. Keys A key is a set of attributes for an entity set such that no two entities agree on all the attributes. We must have a key for every entity set Name Elected Official For an isa hierarchy, only root can have a key. Party isa U.S. District Representative

  25. Key for multiple attributes Must choose one set of attributes First Name Last Name Number Position Baseball Player Birthdate Nationality Salary Team

  26. Key for multiple attributes Must choose one set of attributes First Name Last Name Number Position Baseball Player Birthdate Nationality Salary Team

  27. Key for multiple attributes Must choose one set of attributes First Name Last Name Number Position Baseball Player Birthdate Nationality Salary Team

  28. Weak entity sets Need help to determine key First Name Name Last Number Note arrrow: indicates many to one. Position Baseball Player Birthdate Plays On Team Nationality Salary Name City

  29. Design Techniques Avoid redundancy Say the same thing two ways First Name Name Last Number Position Baseball Player Birthdate Plays On Team Name Team Salary Name City

  30. Design Techniques Avoid redundancy Say the same thing two ways First Name Name Last Number Position Baseball Player Birthdate Team Name Team Town

  31. Design Techniques Don t use entity set if attribute will do Entity lists should either Have some non-key attribute Be the many in a many-one/many-many relationship Plays On Name Baseball Player Team Name City

  32. Design Techniques Don t use entity set if attribute will do Entity lists should either Have some non-key attribute Be the many in a many-one/many-many relationship Name Baseball Player Team

  33. Design Techniques Don t overuse weak entity sets Usually use unique key for each entity set (e.g. UIN, SSN, VIN) Not always possible, though

Related


More Related Content