1 / 37

Basic DB Terms

Basic DB Terms. Data : Meaningful facts, text, graphics, images, sound, video segments A collection of individual responses from a marketing research Information : Data processed to be useful in decision making Pattern of geographical buying habit based on analysis of a marketing research

gareth-patton
Download Presentation

Basic DB Terms

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. Basic DB Terms • Data: Meaningful facts, text, graphics, images, sound, video segments • A collection of individual responses from a marketing research • Information: Data processed to be useful in decision making • Pattern of geographical buying habit based on analysis of a marketing research • Metadata: Data that describes data

  2. Data in Context Large volume of facts, difficult to interpret / make decisions

  3. Information Useful for decision making / interpretation

  4. Metadata Descriptions of the properties or characteristics of the data, including data types, field sizes, allowable values, and documentation (Data Dictionary)

  5. Database • Collection of data in electronic format • A digital library of organization • Managed by one set of software that provides access to all the data • No data redundancy, data inconsistency, poor security, application-data dependency...

  6. Application #1 Application #2 Application #3 DBMS Database containing centralized shared data Database Systems

  7. Database Management System (DBMS) • Database software • Act as an interface between application and physical data files • Support centralization of data • Independent of specific computer programs • small (MS Access), large/popular (Oracle)

  8. Database Models • Hierarchical (Tree) Models • Network Model • Relational Models

  9. Hierarchical database Model • Logically represented by an upside down tree • Each parent can have many children • Each child has only one parent

  10. Hierarchical Database

  11. A schematic diagram of a hierarchical database (a) and a sample part of a hierarchical database showing relationships among different records (b) Hierarchical Database

  12. Network Database Model • Each record can have multiple parents • Composed of sets • Each set has owner record and member record • Member may have several owners

  13. Network Database

  14. A schematic diagram of a network database (a) and a sample of part of a network database showing relationships among different records (b) Network Database

  15. Relational Database Model • A group of related tables • Introduced in 1970 by E. F. Codd of IBM • The most popular model. • Mathematical simplicity • Ease of visualization

  16. A schematic diagram of a relational database (a) and a sample part of a relational database showing different tables (b) Relational Database

  17. Relational database and keys • A relational database is a collection of tables that are related to one another based on a common field. • A field, or a collection of fields, is designated as the primary key. • The primary key uniquely identifies a record in the table. • When the primary key of one table is represented in a second table to form a relationship, it is called a foreign key.

  18. Relating tables using a common field The primary key in the Employer table (EmployerID) is the common field that relates this table to the Position table. PositionID is the primary key in the Position table. The EmployerID field is a foreign key in this table. Primary keys can only have one occurrence in a table. Foreign keys may have multiple occurrences.

  19. Primary Key • Unique identifiernt • Last name vs. SS# • Prevent confusion • Cost of PK • SS# vs. finger print • Entity Integrity Rule • Any primary key is allowed to accept null values.

  20. Foreign Key • An attribute in one table whose values must either match the primary key in another table or be null. • The database must not contain any unmatched foreign key values.

  21. Figure 2

  22. Referential Integrity Rule • Cascade Update Related Fields • Change of PK values in primary table  automatic change of FK values • Cascade Delete Related Fields • Delete of a record in the primary table  automatic delete of all records in the related table that have a matching FK value • See example from the class web site • Primary table: customer

  23. Relational Database Model • Advantages • Easier database design, implementation, management, and use • Ad hoc query capability with SQL • Powerful database management system

  24. Basic Components of DBMS • Data dictionary • DDL (Data Definition Language) • DML (Data Manipulation Language)

  25. A typical data dictionary for a staff file The Data Dictionary (Metadata) - description of every piece of data in database - Maintains all information supplied by the developer when constructing the schema

  26. Data definition language to create a schema in NOMAD • Data Definition Language (DDL) • language to create and modify data • Access table with data type, description, and field properties

  27. A Paradox query by example • Data Manipulation Language (DML) • language that process, update, and retrieve data • Access query

  28. Structured Query Language (SQL) • Standard Query Language (SQL) is the relational model’s standard language. • Another way to generate queries • MS Access: queries by QBE • Other DBMSs: queries by SQL

  29. EmployeeAddressTable SSN FirstName LastName Address City State 512687458 Joe Smith  83 First Street Howard Ohio  758420012 Mary Scott  842 Vine Ave. Losantiville Ohio  102254896 Sam Jones  33 Elm St. Paris New York  876512563 Sarah Ackerman  440 U.S. 110 Upton Michigan  Example of SQL • You want to see the address of each employee: FirstName, LastName, Address, City, and State

  30. First Name Last Name Address City State Joe Smith 83 First Street  Howard Ohio Mary Scott 842 Vine Ave.  Losantiville Ohio Sam Jones 33 Elm St.  Paris New York Sarah Ackerman 440 U.S. 110  Upton Michigan Example of SQL • SELECT FirstName, LastName, Address, City, State FROM EmployeeAddressTable; SQL tutorial: w3.one.net/~jhoffman/sqltut.htm

  31. Conceptual Design Conceptual Schema Logical Design Logical Schema Physical Design Physical Schema Database Design Process Problem Domain

  32. Conceptual Design • The conceptual design is a high level description of the structure of the database, independent of the particular DBMS software that will be used to implement the database. • The conceptual design revolves around discovering and analyzing organizational and users data requirements. • What data is important • What data should be maintained • The major activity of this phase is constructing a data model (Entity-Relationship Diagram).

  33. Data Model: Entity-Relationship Diagram

  34. Why Conceptual Modeling is Important? • Effective Communication Tool • User involvement • Independence from a particular DBMS • Documentation

  35. Logical Design • The logical design is a description of the structure of the database that can be processed by the DBMS software. In other words, the logical design adapts the conceptual design to a specific DBMS implementation model • Thus, the logical design is software-dependent. • Logical Models • Relational Model • Network Model • Hierarchical Model

  36. Physical Design • The physical design describes the storage structures and data access methods used in system. In other words, the physical design is a description of the implementation of the database in secondary memory.

More Related