Database Management Systems INFSY 445 School of Business

Database Management Systems INFSY 445 School of Business Administration Penn State Harrisburg Fall 2005

Feasibility Study n Organizations are looking to: – Reduce costs – Increase sales or revenues – Gain competitive advantage or long-term benefit

Initial Steps in Database Design n Identify exact goals of the system n Talk with users to identify basic forms & reports n Identify data items to be stored n Design the classes (tables) & relationships n Identify any business constraints n Verify the design matches the business rules

Business Rules n Entire database design helps identify and formalize business rules – EX: Do orders come from one customer or can two or more customers jointly place an order?

Model n Simplified abstraction of a real-world system n Conceptual Models = based on user view of the system n Implementation Models = based on the conceptual models and describe how the data will be stored

Data Model n Graphical representation of the components of a database

Relational Database n The most popular database model today n Date in a collection of related files called tables or relations n Each table consists of a set of named columns and any number of rows n Data model depict relationships between classes with a line

The Single Entity Database n Entity or Class: real-world object/thing (person, place, event, or concept) about which a business wants to collect data. EX: CUSTOMER STUDENT SUPPLIER VENDOR WORK ASSIGNMENT

The Single Entity Database n Attributes or Properties: characteristics of an entity; describe the entity and contain the data business wants to store. EX: Name Address City State etc.

The Single Entity Database n Instance: a particular occurrence of an entity. Each instance must be uniquely identified. EX: Name Address City State Zip Code Telephone Interior 101 Pine St. Harrisburg PA 17102 231 -3576 Decorations

The Single Entity Database n Identifier or Primary Key: an attribute or collection of attributes that uniquely identify an instance of an entity. They are underlined.

Data Model to Database n Entity becomes a Table n Entity name becomes Table name n Attributes become Column names n Identifier becomes the Primary Key n Each Instance becomes a Row in the Table

Terms

Entity Integrity Rule n Every row must have a unique, non-null primary key

Associations & Relationships n Represent business rules n Associations are named – Each end is labeled with numbers or *

Associations & Relationships n One-to-one n One-to-many n Many-to-many

Associations & Relationships n Aggregation = a collection of items n Composition = the individual items become the new object n Generalization = lower level classes inherit properties and methods of the classes above them

Associations & Relationships n Reflexive = relationship from a class back to itself

Class Lab n Pg. 64, #1, Rental Canoes Problem