1 / 69

Database Programming Summary

Database Programming Summary. John Lamertina. Reference: Riccardi, G, Database Management with Web Site Development Applications , Addison-Wesley, 2003. Content. Part I: Introduction Chapters 1 and 2 Part II: Design Information Systems Chapters 3 and 4

fleta
Download Presentation

Database Programming Summary

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 Programming Summary John Lamertina Reference: Riccardi, G, Database Management with Web Site Development Applications, Addison-Wesley, 2003

  2. Content • Part I: Introduction • Chapters 1 and 2 • Part II: Design Information Systems • Chapters 3 and 4 • Part III: Design Relational Databases • Chapters 5, 6, and 7 • Part IV: Manipulate Relational Information • Chapters 8 and 9 • Part V: Create Interactive Web Sites • Chapters 10, 11, and 12

  3. Chapter 1Introduction to Information and Database Systems I. Introduction

  4. How Do Databases Represent Information? • The physical database: • a collection of files containing the data content • The schema: • a specification of the physical database’s information content and logical structure • The database engine: • software that lets people access and modify the database contents • The data definition and manipulation languages: • programming languages, such as Java or SQL (Structured Query Language), that let software developers define the schema and access the database

  5. How Do Databases Represent Information? • Relational database management system (RDBMS) • Tables of data • Schema • Name of table • Names and types of attributes • Contents • Row is a fact • Attribute value is a characteristic

  6. Example of storing customer information

  7. Chapter 2Internet Information Systems

  8. Components of the Web • Web browser • Formats and displays Web pages • Requests pages from Web server • Collects user inputs and sends them to server • Web server • Sends Web pages to browser • Accepts and processes user input • Sends requests to information server • Information Server • Accepts requests from Web server • Manages complex information resources • Contains database server

  9. Web Server • A computer program that services browser requests • A Web server is software that knows how to service requests • A Web server computer is a computer that executes a Web server program • Browser request for a specific HTML document may be serviced from the Web server computer’s disk

  10. Generating HTML Documents • An Information Server may be a computer that generates HTML documents for storage in a Web server • New York Times has regular updates of its pages from an information server that contains all of its news stories and other information • Someone requests the recreation of the home page (index.html)

  11. Sample SQL Statement • SQL (Standard Query Language) is the language that is used to communicate with database servers • The information for the sales receipt entries is produced by sending this SQL statement to the BigHit Online database server • select Movie.movieId, Movie.title, Sale.format, Movie.dvdPrice, Movie.tapePrice, Sale.quantity, Sale.cost from Movie, Sale where Movie.movieId = Sale.movieId and Sale.accountId = 101 and Sale.saleDate = 'Mar 5, 2002'

  12. Chapter 3Representing Information with Data Models II. Design Information Systems

  13. What is a Data Model? • A data model is a precise description of information content • Types of data models • Conceptual: in terms that users will understand • Logical: in terms that a relational database system will understand • Physical: in terms of the underlying computer hardware and operating system • Database schemas • Schema is another word for model that implies that it adheres to a particular strategy for defining modelsc

  14. Organizing Information • To understand data modeling we must learn many new technical terms • Entity • A thing (object) that is of interest to an information system • An abstraction of the object that contains those characteristics that are important to the information system • Entity class • The common characteristics that represent a set of entities • The common characteristics of a particular set of entities that make them distinct from other entities • Attribute value • A characteristic of an entity • The value of a particular characteristic • Attribute • A characteristic that helps to describe an object • Examples • Entity: A customer, or the characteristics of a customer that are of interest • Entity class: All of the potential customers, or the information about them • Attribute: hair color or last name • Attribute value: hair color is brown or last name is ‘Breaux’

  15. Video Customer Rental Discovering Relationships • A relationship is a connection between 2 entities • Customer Jane Block rents the video with videoId 90987 • A relationship type between 2 entity classes represents the possibility that two entities may be related • A customer may rent a video • Attributes and relationship types are not the same • The customer accountId is not an attribute of the Video entity class • What are the relationships, entities and attribute values in this video rental receipt? • What entity classes, attributes and relationship types are implied by the video rental receipt?

  16. Case in Point • Determining entity classes, attributes and relationship types for BigHit Online video sales • Process • Evaluate statement of goals for information system • Define entity classes • Define attributes for entity classes • Define relationship types • Determine cardinalities of relationship roles • Add attributes to relationship types as necessary • Evaluate entity classes, attributes, and relationship types for clarity, accuracy, and completeness

  17. Chapter 4Data Modeling with Entity-Relationship Diagrams

  18. Terminology Recall from Chapter 3: Entity Class: blueprint for individual objects (instances / entities); the common characteristics for a collection of entities Attributes: properties (characteristics) that describe an entity Relationship Type: representation of the possible association between two or more entity classes (e.g. customer may rent a video; an employee may manage a store) Cardinality Constraint: limitation on numbers (e.g. one, many, or other specific min or max) M

  19. Entity-Relationship Modeling • An E-R model is a data model that includes • Entity classes • Attributes of each class • Relationship types between classes • Constraints • Types of attributes • Designation of key attributes • Cardinalities of relationship types • An E-R Model is typically represented graphically • E-R diagram, the technique we use • UML diagram, an emerging standard for specifying E-R models and software design

  20. Entity Relationship Diagrams • Sample diagram for entity class Customer

  21. Relationship Types • Example of representing relationship type Owns between classes Store and Video

  22. Participation constraints Cardinality constraints Constraints on Relationship Types • Example of cardinality and participation constraints 1 Store may own Many Videos A particular Video must be owned by exactly 1 Store 1 Store owns Many Videos A Video is owned by no more than 1 Store

  23. Modeling Video Rentals • Examples of current rentals and previous rentals • Differences are in cardinalities and attribute names A customer rents a video. 1 Customer may rent Many Videos; a particular Video may be rented by exactly 1 Customer (at a time) Customers previously rented videos. A Customer may have previously rented Many Videos; A Video may have previously been rented by Many Customers

  24. Relationships  Entities Relationship sometimes better represented as an equivalent Entity. E-R Symbols: • Entity Class:nouns (instances / entities) • Attributes:adjectives (properties / characteristics) describe an entity • Relationship:verbs (e.g. customer may rent a video; an employee may manage a store) Rents Rental

  25. Modeling Video Rentals as an Entity Class • Diagram shows entity class Rental and its relationship types with Customer and Video • Note cardinalities and participation constraints • A Rental entity cannot exist without being related to both a customer and a video • Problem occurs because Rental has no key attribute (no inherent primary key) Note that we can now identify and collect attributes of a rental.

  26. Weak Entity Classes • A weak entity class is • An entity class with no key of its own • An entity class whose entities cannot exist without relation to other entities • An identifying relationship type is • A relationship type that determines the keys of the weak entities • The weak entity class always has a to-1relationship with owner entity class.

  27. Previously Rented M M Customer Video dateReturned cost dateRented Rental History We shall see that a Many to Many relationship is modeled by applying a pair of 1-to-Many relationships.

  28. dateRented Rental History Options (a) Thus, a Previous Rental is associated with exactly one Video. Convert Relationship to an Entity Customer PreviousRental Video M M 1 Has Has 1 Identifying Relationship associates Weak Entity to Owner Entity with to-1 Cardinality videoID customerID Option1: composite primary key: videoID and customerID – but this does not allow customer to rent same movie twice Customer PreviousRental Video M M 1 Has Has 1 videoID Option2: composite primary key: videoID and dateRented – but this does not allow a video to be rented twice on the same day Option3: use dateTimeRented instead of just dateRented

  29. Rental History Options (b) Customer PreviousRental Video M M 1 Has Has 1 videoID dateRented Option4: allow inactive Customers to be deleted by changing participation constraint to optional

  30. Auction Web SiteProblem #10, page 87 An Item can be won by a single highest bidder. A Seller may offer many Items. SellerID ItemID Seller Item M 1 Offers PriceOpen MinWinBid TimeEnd 1 1 An Item can be bid upon by many bids. Customer is SuperClass of Seller & Bidder Placed on The Highest Bid may win Item (if that bid is at least of a minimum value) Wins Highest Bid BidderID M 1 M Bidder 1 Makes Bid ItemID Amount BidderID Many Bids may be placed on an Item. Time A Bidder may make many Bids. Bid is a weak entity class – it does not exist without an item. Thus, item has to-1 cardinality in relation to bid.

  31. Chapter 5Defining Relational Data Models III. Design Relational Databases

  32. Basics of the Relational Model • The relational model represents information in tables (called relations) • Each table represents a set of entities • Each column of a table represents the attribute values of the entities • Each row of a table represents a single entity • A database schema is a collection of table definitions (relation schemas) • A relational database is a collection of tables • Each table stores objects for a single relation schema

  33. Relation Schemas and Keys • The rows of a relational table are unique • No 2 rows have the same values for all of the attributes • A key is a collection of attributes in which • No 2 rows have the same values for all attributes in the key • Every table must have a key • Why? • A relation schema is the specification of the structure of a table • Name of the table • Name and type of each attribute • Declaration of the key • A key declaration is a constraint • A table is not allowed to have 2 different rows that have the same value for the key • Database systems enforce key constraints • By blocking any attempt to modify a table that will result in a violation of the key constraint

  34. Relation is not Relationship • Be careful of these two words • Relation • Relationship • A relation is a table that contains a set of entities • A relationship is a connection between two entities • We must be very careful to use the correct word

  35. Representing Entity Classes • For each strong entity class in your E-R model create a relation schema: • Rule 1a: Define a relation schema by the same name. • Rule 1b: For each single-valued attribute of the entity class • create an attribute by the same name in the relation schema and specify a type for the attribute • Rule 1c: Define the key of the new relation schema as the key of the entity class • If the entity class key consists of multiple simple attributes, the key of the relation schema will be that set of attributes. • Underline your selected key attribute in each schema in order to identify the key.

  36. Composite Attributes • Rule 2. For each composite attribute of a strong entity class • create an attribute in the relation schema for each component attribute • If appropriate, use the name of the composite attribute as a prefix for each of the component attribute names • Schema: Customer (accountId string, lastName string, firstName string, street string, city string, state string, zipcode string, balance number)

  37. One-to-Many RelationshipTypes • For a one-to-many relationship type • Add the key attributes of one entity class to the other entity class (foreign key attributes). • Add the foreign key attributes to the class whose cardinality is 1 • Rule 3: For each one-to-many relationship type R between subject class S and target class T • add the key attributes of class S to class T as foreign keys • Name the attributes using the role that S plays in relationship type R • Add the attributes of the relationship type R to target class T.

  38. One-to-One Relationship Types • The foreign key attributes may be added to either schema • Each entity class is to-one in the relationship type • Choose which class to include the foreign key attributes • One option is to try to minimize the number of null values • Rule 4: For each one-to-one relationship type between two classes, choose one class to be the subject and one to be the target • Add the key attributes of the subject class to the target schema as foreign key attributes • Add the attributes of the relationship type to the target schema, just as in Rule 3

  39. Many-to-Many Relationship Types • Many-to-Many relationship types between 2 classes cannot be represented as simple attributes in either related table • Rule 5: For each many-to-many relationship type R between classes S and T • Create a new relation schema R • Add attributes to represent the key of S and the key of T as foreign key attributes • The key of schema R is the combination of those attributes • Add the relationship attributes to schema R, as in Rule 3 • Schema: WorksIn (ssn string references Employee, storeId number references Store)

  40. Chapter 6Defining Relational Databases with Microsoft Access

  41. Chapter 7Improving Relational Schemas and Normalization

  42. Redundancy and Anomalies in Relation Schemas • Anomalies occur when data is inconsistently updated • Redundancy of values is the source of anomalies • Update anomalies are of three types: • Modification • Deletion • Insertion

  43. Redundancy and Anomalies in Relation Schemas Update Anomalies: • Modification anomaly caused, for example, by changing title, genre, length or rating in any one or two (but not all three) of the green-highlighted rows • Information about same movie is different in different records (i.e. it is inconsistent) • Deletion anomaly caused by deletion of row with videoId1243 (pink) • Information about movie is deleted along with video • Insertion anomaly caused by last row (blue) • Length and rating are inconsistent with other rows

  44. Functional Dependencies Between Attributes • A functional dependency is a strong connection between two or more attributes in a table. • one attribute is functionally dependent on another attribute when any two rows of the table that have the same value of the second attribute must have the same value for the first • Example: movieId determines title, genre, length, rating • Each row with movieId 123 has the same values for other attributes • FD2: movieId  {title, genre, length, rating} pointed attributes are dependent on movieId movieIddetermines the values of the pointed attributes Thus, we have four attributes functionally dependent on movieId

  45. Primary Keys • Primary Keys define a kind of Constraint • Non-null • Unique • Primary Keys define a kind of Functional Dependency • Primary key value uniquely identifies an entity • The total of all other attributes of an entity is dependent on the entity’s unique identifier (i.e. the entity’s Primary Key) • Constraints are, thus, Functional Dependencies.

  46. Normalization • Normalization is the process of transforming some objects into a structural form that satisfies some collection of rules • Any schema that is in normal form is guaranteed to have certain quality characteristics • Each normal form has a rule that describes what kinds of functional dependencies the normal form allows. • Normalization is the process of transforming schemas in order to remove violations of the normal form rules. • Normalization is applied independently to each relation schema in a database schema. • A a database schema is said to be in normal form if each of its relation schemas is in the normal form.

  47. Normalization: Introduction • A process for designing “good” table structures • Good structures: • Avoid data redundancy and inconsistencies • Insure that all data can be retrieved (no orphans) • Facilitate updates (information is stored only once) • Ensure that up-to-date information can readily be accessed by all users

  48. Project Design Sample: Rob, Database Systems,Course Technology, 2002, p 177-187 Original LayoutPrior to Normalization Proj_Num Proj_Name Emp_Num Emp_Name Job_Class Chg_Hour Hours 15 Evergreen 103 June A. Elect.Eng. 84.50 23.8 101 Jon G. Dat Des. 105.00 19.4 105 Alice K. Dat. Des. 105.00 35.7 18 Amber 114 Ann J. App Eng. 48.10 24.6 101 John G. Dat Des. 105.00 14.5 Repeating Groups Note that an employee can be assigned to multiple projects; and that to any project multiple employees can be assigned. Duplicate & Inconsistent Data Null Primary Key

  49. Job_Class Chg_Hour Convert to 3NF • Remove all fields from the 2NF table that depend on another non-key field, and place those fields in a new table that uses the non-key field as a primary key. • E.g. Chg_Hour depends on Job_Class; so move those fields to new table JOB with primary key, Job_Code. EMPLOYEE Table JOB Table

  50. After Phase 3 Normalization(3rd Normal Form) Primary Key Proj_Num PROJECT Table Proj_Name Emp_Num (Project Leader) Non-key Field Foreign Key Job_Code JOB Table Job_Desc (Job_Class) Job_Chg_Hr Transitive Dependency desired for historical accuracy of costs. Emp_Num EMPLOYEE Table Emp_Fnam Emp_Lnam Emp_Hire_ Date Job_Code Assgn_Num ASSIGN Table Proj_Num Emp_Num Assgn_Date Assgn_Hrs Assgn_Chg_ Hr Isolate Transitive (Indirect) Dependencies. (JOB Table still in 2NF)

More Related