1 / 25

File and data base concepts

File and data base concepts. DB design. Webcast will cover. Databases Entities Basic ERD PK & FK Basic Database Design. DBMS. DB: subsystems. DDL. User. OS. DBM. Data. DML. Base. Application. program. DB logical structures: hierachical network relational.

emery
Download Presentation

File and data base concepts

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. File and data base concepts DB design

  2. Webcast will cover • Databases • Entities • Basic ERD • PK & FK • Basic Database Design

  3. DBMS DB: subsystems DDL User OS DBM Data DML Base Application program DB logical structures: hierachical network relational

  4. Views, Schema and Subschemas 1 2 5 4 7 9 10 8 15 12 17 13

  5. Types of databases • Hierarchical • Network • Relational • Object-oriented

  6. DBMS Has Two Parts • DML: Data Manipulation Language • DDL: Data Definition Language

  7. Structured Query Language (SQL) Syntax SELECT col names FROM tablenames WHERE conditions

  8. Employee name Job Title Roomlocation Extension Andazola, Genevieve Secretary, Financial Aid 1-308 2245 Secretary, Student Carter, Georgia 1-224 4716 Affairs Secretary, Placement Durbin,Martina 1-110 4235 Service Secretary II,Student Ezikian, Olivia 1-224 4714 Affairs Director,Placement Hart, Dr. John 1-110B 4234 Services Jacobs, Harry M. Director, Financial Aid 1-308 2244 Assistant Director, Jones, Madalena 1-224 4715 Student Affairs Smith, Rose Secretary, Admissions 1-104 4823 Vice-President, Student Wells, Dr.Janet 1-224 4713 Affairs York, Richard Director, Admissions 1-104 4822 Example

  9. SQLEMPLOYEE (EMPLOYEE_NAME, JOB_TITLE, ROOM_LOCATION, EXTENSION) Ex: get the names of all employees SELECT employe_ename FROM EMPLOYEE; Ex: Get employee information that have location I-308 Select * FROM EMPLOYEE WHERE ROOM_LOCATION = ‘I-308’;

  10. PRIMARY KEY (PK) a unique identifier guarantees that each row of a relation can be uniquely addressed

  11. BANK ACCOUNT ( ss#, cust name, cust address) What’s the PK?

  12. BANK ACCOUNT( ss#, cuts name, cust address) • SS# can be a PK since it can not repeat in this table What happens if we add cust_account to this table BANK ACCOUNT( ss#, cuts name, cust address, cust-account)

  13. BANK ACCOUNT( ss#, cuts name, cust address, cust-account) • ss# can NOT be a PK if we assume a customer can have multiple accounts For customer smith Table may look like 111-11-1123 smith UB CHK112 111-11-1123 smith UB SAV123 • Note now ss# can NOT be a PK since ss# values are repeating • this violates the definition of PK, it returns two rows!!!!

  14. BANK ACCOUNT( ss#, cuts name, cust address, cust-account) • Can cust-account can be a PK?Only if two customers can NOT share the same account, i.e., no joint account

  15. BANK ACCOUNT( ss#, cuts name, cust address, cust-account) • PK must be a combination of TWO attributes • In this case SS# and cust-account Should be PK for this relation

  16. FK • Relates two tables • Used to maintain INTEGRITY (cross reference) An attribute in ONE table must match values in another table where that attribute is a PK

  17. VENDOR and PRODUCT tables VENDOR (Vend_CODE, VEND_CONTACT, VEND_ADDRESS, VEND_PHONE) PRODUCT (PROD_CODE, PROD-DESCRIPTION, PROD_PRICE, PROD_ON_HAND, VEND_CODE) Red implies PK for each table Purple key is PRODUCT table is FK to VENDOR table

  18. Entities An object of interest • Person • Place • Event • Concept Ex: Student Order Customer

  19. Relationship Exists between entities With entity itself Binary between TWO entities Ternary between THREE entities

  20. Relationship among entities Assume: A team can have many players and a player can play for ONLY ONE team TEAM PLAYER TEAM------- > PLAYER

  21. Relationship? STUDENT CLUB Can a student belong to many clubs? Yes then 1:m Can a club have many student members? Yes then 1:n M:N STUDENT <-------->CLUB

  22. DB Design Concepts how to represent entities and relations ENROLLMENT (SID, SNAME, SADD,CID,CNAME,GRADE,SEMESTER)

  23. Data Redundancy

  24. Remove redundancy STUDENT (SID,SNAME,SADD) CLASS(CID,CNAME) GRADE (SID,CID,GRADE,SEMESTER)

  25. NORMALIZATION: 1. represent each ENTITY as a TABLE 2. select the PRIMARY KEY 3. assign entity ATTRIBUTES to FIELDS 4. represent an ONE-TO-MANY relation by a FOREIGN KEY in the MANY table. 5. represent MANY-TO MANY relations as a NEW TABLE. Use FOREIGN KEYS to identify entities involved. The combination of foreign keys is the PRIMARY KEYof the new table. Assign the common attributes to fields. DB Design principles

More Related