Transcript Primary Key
MS Office & Internet I Database Concepts Part 01 & 02 The Database Environment The Database Environment consists of all the parts and pieces that makeup a database system The Database Environment DUNSHA D Data U Users N Network S Software H Hardware A Administration What is a Database? A Database is a: Collection of Related Data for A Known Group of Users that meet Specific Requirements and Models or Represents the Real World Database Structures Relational Type Object Type MS Access Type Relation Table Table Tuple Row Record Attribute Column Field Primary Key Primary Key Primary Key Foreign Key Foreign Key Foreign Key Relation Rules No Duplicate rows in a Table No order of Rows No order of Columns Relationships Defines how Tables are related to each other in the database Relationships Relationships are created by using a Foreign Key Relationships Primary Key Must be UNIQUE Is MANDATORY Is UNCHANGING Is CONTROLLED BY IT DEPT Relationships Foreign Key (FK) Is the Primary Key (PK) of the parent table Relationships Rules: No Part of the Primary Key can be NULL (We call this enforcing Entity Integrity) NULL = Nothing (It is not Zero (0) it is nothing) Relationships Rules: A Foreign Key can be NULL It must be a PK in the related table (We call this enforcing Referential Integrity) Database Model Represents Reality Has Relations (Tables) that: Equals one Entity Type per Table Each Row represents only one occurrence of the Entity Each occurrence (Row) is Unique Database Model A Primary Key and Foreign Key may be Composite Keys Made up of more than one Column (Attribute) Example Table (w/Data) Primary Key (Underlined) ANAME AFAMILY WEIGHT Candice Camel 1800 Zona Zebra 900 Sam Snake 5 Elmer Elephant 5000 Leonard Lion 1200 Relation (Table) Attributes (Columns) Tuples (Rows) Example Relationship Foreign Key Zoo-Member MID MNAME MADDR ANAME 171 N. Harrison 1400 Blush Rd Zona 144 J. Montagano 1108 5th Ave Leonard 194 J. Spence 1244 Lark Ln 303 E. Wingate 5222 Gains Dr 101 H. Yarchun 177 Beach Rd 270 K. Steeg 140 Crystal Dr Zona 291 S. Ackerman 1172 Park Dr Sam 301 K. Snyder 196 279th Ave (Circled) Animal ANAME AFAMILY WEIGHT Candice Camel 1800 Candice Zona Zebra 900 Candice Sam Snake 5 Elmer Elephant 5000 Leonard Lion 1200 MS Access MS Access is a Personal or Small Business Relational Database It is limited in scope but powerful for its intended purpose Microsoft SQL Server This is the Enterprise or Large Business Relational Database which is very powerful and complete in scope ORACLE Database This is the Enterprise or Large Business Relational Database which is very powerful and complete in scope Taught at Rio in courses: IT 20303 DBMS Concepts IT 20803 Database Communications IT 21003 Database Administration Can lead to Oracle Certification (OCA) or the URG Database Technology Certificate MS Access We will build a database in Class using MS Access utilizing all of the parts and pieces: Tables Rows Columns PK’s FK’s Questions End Part 01 Entity-Relationship Diagrams Logical Database Design What is a Data Model? A way to represent reality A schematic of data items and relationships A “blueprint” for the database Entity-Relationship Diagrams The Entity-Relationship Approach Represents reality using well-defined graphics and rules Basic building blocks are: “things” (entities) and relationships Member M 1 Adopts Animal Relational Database Theory • Entity-Relationship Model: Basic Concepts – Entity • Thing, Object, Concept of interest to the enterprise • Each occurrence can be uniquely identified Relational Database Theory • Entity-Relationship Model: Basic Concepts – Attribute • Property of an entity • Column Relational Database Theory • Entity-Relationship Model: Basic Concepts – Relationship • Association between two (or more) entities Relational Database Theory • Entity-Relationship Model: Basic Concepts – Entity Identifier • Attribute(s) whose value uniquely identifies an entity • Primary Key Relational Database Theory • What is an Entity? – Physical entity types • • • • • Person Building Machine Book Usually Singular Relational Database Theory • What is an Entity? – Conceptual entity types • • • • Contract Account Order Course Relational Database Theory • What is an Entity? – Event entity types • • • • • Transaction Shipment Reservation Phone Call Seminar Offering Relational Database Theory • Entity-Relationship Model: Diagrams – Example: Member Adopts Animal – Soft Rectangle represents entities • Noun • Singular – Connecting Line represents relationships • Verb Relational Database Theory • Relationships have Characteristics – A relationship has Cardinality (Degree) One-to-One One-to-Many Many-to-Many Relational Database Theory • Each entity’s participation is Mandatory or Optional • Cardinality & Optionality are based on business rules Mandatory Optional Relational Database Theory • One:One Relationship – One Member adopts one animal – One Animal is adopted by one member Member Adopts Animal Relational Database Theory • One:Many relationship – One member adopts one animal – One animal is adopted by many members Member Adopts Animal Relational Database Theory • Many:Many relationship – One member adopts many animals – One animal is adopted by many members Member Adopts Animal Relational Database Theory • Optionality: Participation in a Relationship Zoo Employee Cares for Animal Relational Database Theory • Mandatory – Every instance of the entity MUST participate in the relationship – Example: • Every animal is cared for by at least one employee Relational Database Theory • Optional – An instance of the entity CAN participate in the relationship – Example: • Some employees do not take care of animals E-R Diagrams • Guidelines to Develop an E-R Diagram – Identify the Major Entities – Identify the Attributes for each entity – Determine the Unique Identifier(s) – Identify the Relationships – Assign Cardinality – Determine Optionality – Resolve M:N Relationships E-R Diagrams • Mapping the E-R Diagram to the Relational Database – Each entity becomes a Table – Each attribute becomes a Column – Unique Identifier becomes the PK – Each 1:M becomes a FK on the Many Side E-R Diagrams • Practice 01 – A company has ten departments – A company has five divisions – A company has one hundred employees – Each employee must work for one department – Each division has two departments E-R Diagrams • Practice 02 – A company has twenty employees – Each employee works for a department – There are two departments in the company E-R Diagrams • Practice 03 – A company has three divisions – A company has one manager per division – Each manager is in charge of one committee E-R Diagrams • Practice 04 – A company has a sales department with fifteen salespersons – Each salesperson works for the sales department – Each salesperson is supervised by one manager – The managers may not have an salesperson to supervise Questions End Part 02 Assignment 04 In-Class Project Develop a Database in MS ACCESS 2003 Choices Are: Music Library Book Library Club or Organization