1 / 22

Database Design

Database Design. MIS 320 Kraig Pencil Summer 2013. Game Plan. Introduction Data base design Database scenario Key questions/answers. A. Introduction. Remember … ? CNN: Internet is “Top innovation” of past 25 years ( 1/18/05 ) But Kraig Pencil says …

zaina
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 MIS 320 Kraig Pencil Summer 2013

  2. Game Plan • Introduction • Data base design • Database scenario • Key questions/answers

  3. A. Introduction Remember … ? CNN: Internet is “Top innovation” of past 25 years (1/18/05) But Kraig Pencil says … The relational database is equally important Really? Why? The Internet would not be nearly as successful without relational databases providing rich information.

  4. A. Introduction You can ask a database questions • How many cans of chicken soup do we have in inventory? • How large is the average pay raise in each department? • Who are my best/worst customers? • What else have I-Pod buyers also purchased? • Is this airline passenger a known terrorist? • Who has friended Gayle Zhang? • Who else might Gayle want to consider friending? With the right data and a well-designed database, you can ask anything.

  5. A. Introduction Who works with databases? • Database programmers • Database analysts • Database designers • Database administrators • Data administrators • Website designers/programmers • Many non-IT users! • Marketers • Financial analysts • Human resources managers …. And more

  6. A. Introduction • What does an organization do with data/information? • Capture, cradle, and process transaction data/information • e.g., TPS • Use information to make decisions • e.g., DSS • “Oversee” the information • e.g., Who can view or use what info?, How to back up info?, How long to retain info? • What can help support the above? • A database management system

  7. B. Business Scenario • Is data management a problem? For generations we stored information in filing cabinets, then in Excel spreadsheets. But as the amount of data has grown those methods have become … • Inefficient – and expensive – to store and gather data • Unable to support for decision making basedon data  A database management system is needed to support business !!!

  8. B. Business Scenario • Northwind Incorporated • Distributor of food and beverage products • Customers: Restaurants and specialty food stores • Suppliers: Come from around the world • In the lab and lectures, you will seehow efficiently a database management systemcan provide rich information to theorganization.

  9. C. Steps of Database Design • What kinds of “things” does an organization want to collect information about? • Determine the Entities • For Northwind Inc, the “entities” of interest include: • Customers • Suppliers • Employees • Products • Etc.

  10. C. Steps of Database Design (cont.) 2. What specific information about each entity is important? • Determine the attributes for each entity • Example: For the Employee entity, attributes may include • Employee name • Address • Hourly rate • Etc. • Who is this guy doing the interview? • A “business analyst” For each employee, we keep track of … President of Northwind

  11. C. Steps of Database Design (cont.) 3. How do we organize the data? Which type of database “model” shall we use? • Assume: Use the most popular model • Relational model • “Relation” refers to ??? • Data is organized as a linked set of tables • An example of a relational database management system that you are using in MIS 320 : ___________

  12. Northwind Database in MS Access Sample Entity-Relationship Diagram (aka an “ERD” or “ER diagram”)

  13. B. Database Questions (cont.) 4. What are the important aspects of a data table? • Terminology for data tables • Field (i.e. column) • Attribute of the entity • Record (i.e. row) • An instance of an entity • File (i.e., table) • Collection of records for an entity • Primary key • Unique identifier for each record • Often a unique “ID code” is created • Example: See next figure Find all records in the Customer file for which the customer’s name field is Serena Lewis

  14. Database Tables What are examples of a) Field, b) Record, c) File, and d) appropriate Primary Key ?? Supplier Table Product Table

  15. C. Steps of Database Design (cont.) 5. What are the relationships between the entities? • A database involves tables that are linked together • When building a database, we need to know how to link the tables • i.e., We need to know how the entities are related! • e.g., A Supplier provides a Product. • If we know who the supplier is, wecan find all the products they have supplied to us. • For any product, we should be ableto find the supplier’s address. • Develop a “blueprint” diagram for database design • Entity-relationship diagram

  16. B. Database Questions (cont.) 5. What are the relationships between the entities? (cont.) • Possible “relationship types” • A) one to one, B) one to many, c) many to many • “one” means “at most one” • “many” means “more than one” • Example: • Entities: Supplier, Product • What is the relationship type? We are told that: • A supplier can provide many different kinds of products. • A product can be supplied by a max of one supplier. • Entity-Relationship Diagram: one many ??? ??? Supplier Product Provides

  17. B. Database Questions (cont.) 5. Relationship symbols: these all mean the same thing The textbook used “crowsfoot” symbols Access uses “1 – ∞” symbols Access uses “ – ∞” symbols One Many Ignore these “inner” symbols

  18. B. Database Questions (cont.) 5. (cont.) • Foreign key • Serves as a “link” between data files/tables • A field in one file/table that serves as a primary key in another file/table • How to determine the foreign key? • For a “one to many” relationship, the primary key on the “one” side of the relationship is added to the table on the “many” side • See examples

  19. Database Tables: Northwest Inc. Supplier Table Product Table Category Table

  20. Northwind Database in MS Access: Primary/Foreign KeysWhere are the primary keys? The foreign keys?

  21. C. Steps of Database Design (cont.) 6. What do we do with the entity-relationship diagram? (cont.) • ERD can be reviewed with client (Is the design correct for the client’s needs?) • Database design can be used to build the database • e.g., Build database in MS Access • Can database design get more complicated than this? • Yes! • But … the previous example will suffice for MIS 320 • The lab database projects will typically focus on “one to many” types of relationships

  22. Terminology Summary The terms in each row have very similar meanings. For this course, learn all of the terms, but do not be concerned about the nuanced differences between terms in the same row.

More Related