Database Systems: Understanding Different Architectures and Objects, Slides of Fundamentals of E-Commerce

Download Database Systems: Understanding Different Architectures and Objects and more Slides Fundamentals of E-Commerce in PDF only on Docsity! Introduction to Databases Docsity.com Learning Objectives • To describe a database • Understand hierarchical, relational, and object- oriented databases • Understand the process for developing a database system • Create entity relationship models to describe a database design • Derive database tables using an entity relationship model 1-2 Docsity.com Overview of Databases (cont’d) • In the case above (data file approach), each application must have its own tools for: – Inserting new data – Viewing existing data – Updating existing data – Deleting existing data 1-5 Docsity.com Overview of Databases (cont’d) • Database Management System (DBMS) were developed – Single set of programs to perform basic data handling procedures – Data stored at a central location – Examples • Oracle • SQL Server 1-6 Docsity.com Overview of Databases (cont’d) Student Schedule Application Student Transcript Application Course Schedule Application 1-7 Docsity.com Database Objects (cont’d) • Attribute – Data value of an entity – Examples • StudentID • Firstname • Relationship – Used to represent a connection among related entities, like a link 1-10 Docsity.com Database Objects (cont’d) FIGURE I-3 Example entities, attributes, relationship, and entity instance Attributes — Entity —— instance Entities Relationship UniversityStudent UniversityCourse (| =a | Enrollsin i StudentID | CourselD | StudentLastName a CourseName || StudentFirstName | CourseTitle || StudentMI Nelson| Amber Docsity.com Database Objects (cont’d) • Naming conventions – Use descriptive names that are self- documenting – Use two words rather than one for all entities and attributes – Avoid reserved word conflicts – Begin each new word with a capital letter – Omit spaces between words 1-12 Docsity.com Hierarchical Databases FIGURE 1-4 Hierarchical database structure UniversityStudent Student Student p ecinama FirstName StudentMI Pointers to Course Data Nelson Amber Ss 9 ® Hernandez Joseph P ® Myers Stephen R ° ¢ UniversityCourse CourseName | CourseTitle MIS 290 Intro. to Database ~ Applications ~ MIS 304 Fundamentals of ~ Business Programming _4 MIS 310 Systems Analysis & Design ~ 1-15 Docsity.com Hierarchical Databases (cont’d) • Problems with Hierarchical Databases – Difficult to move to new storage medium • Data is physically dependent on its location on the storage media – Changes to database structure require rewriting of programs • Time-consuming • Expensive 1-16 Docsity.com Relational Databases Understand or memorize this info! • Tables – Stores the data • Records (like a row in a table) – Contains data about an individual entity instance • Fields (table columns) – Attributes that are associated with individual data values • Key fields – Create relationship among records in different tables 1-17 Docsity.com Relational Databases – Key Fields (cont’d) • Foreign keys – Create a relationship between two tables – A primary key field in one table (parent) and acts as a foreign key in another table (child) – Enforces referential integrity • When a table is created with a foreign key, all foreign key values must exist in the parent table • Populate the table with primary keys first!!! 1-20 Docsity.com Relational Databases – Key Fields (cont’d) • Composite keys – A unique primary key created by combining multiple key fields in a table – Made up of foreign keys fields that are primary keys in other tables – E.g., CustomerID and OrderID – Always optional – You will use them in all projects 1-21 Docsity.com Primary keys e Relational Databases — Key Fields (cont’d) FIGURE 1-6 Creating relationships using foreign keys Child table —— UniversityInstructor ' Instructor Instructor InstructorlD eeinare aieiNenae —— Parent table 1 || Black Greg 2 || Mcintyre Karen + 3 | Sarin Naj T Foreign keys UniversityStudent p---- Lo =| ft I Student Student | : \ StudentID acta FirstName StudentMI tl AdvisorID a ! 5000 Nelson Amber S ae | 5001 Hernandez Joseph P if 1 T 5002 Myers Stephen R 113 1 T 1-22 Docsity.com Object-Oriented Databases • For your information only (not on the test) • Object – Similar to an entity – Stores data as well as methods • Methods - the programs that interact with the data • Object instance – Similar to a record – Refers to the single unique object 1-25 Docsity.com Object-Oriented Databases (cont’d) • Object class – Collection of similar objects – State - specifies its attribute values and the relationships of all object instances within the class – Behavior - represents the actions of its instances within the database application 1-26 Docsity.com Object-Oriented Databases (cont’d) • OODBMS - Manage data objects – Define the object classes, their associated attributes, and methods – Write commands to create individual object instances for each data item • Each object has specific attribute values and relationships with other objects 1-27 Docsity.com Initiation Phase • Analyze the current data-processing systems • Define the problem or opportunity that the proposed system addresses • Justify the need for the proposed system • Output - Project proposal 1-30 Docsity.com Initiation Phase (cont’d) • Project proposal includes: – Organizational objectives – Organizational data overview – Organizational structure chart – Problem identification and strategic rationales 1-31 Docsity.com Analysis Phase • Define the scope of the proposed system • Identify data inputs and outputs • Identify system operations • Create prototype input screens and output reports • Create the data dictionary • Output – Initial study 1-32 Docsity.com Design Phase • Conceptual design – Transforms the data dictionary into a detailed data model (independent of database structure) • Logical/physical design – Transforms the conceptual design into physical database design that programmers can implement using a specific database • Design the system applications • Output – Project design document 1-35 Docsity.com Design Phase (cont’d) • The project design document includes: – Entity relationship model – Database table or object definitions – User interface input and output screen and printed report prototypes – Integrated application design and menu design – Implementation schedule 1-36 Docsity.com Implementation Phase • Creating the database and defining the database objects or tables • Loading a small set of test data • Creating the user applications • Testing and reviewing the user applications using the test data set 1-37 Docsity.com Maintenance Phase • Upgrading the DBMS software as new releases become available • Correcting errors in existing database applications • Creating new applications as user needs evolve • Continually monitoring database performance • Retuning the DBMS as necessary 1-40 Docsity.com Maintenance Phase (cont’d) • Maintaining user accounts to control database access • Creating new accounts as new users come online • Monitoring data storage space • Performing backup and recovery operations • The longest phase in database development process 1-41 Docsity.com Entity Relationship Modeling • Visually represents entities, attributes and relationships • Output of the conceptual design phase of database development (DBMS independent) • Used by developers to: – Identify structure of relational database tables and data objects – Communicate findings to system users 1-42 Docsity.com Entities and Attributes (cont’d) • Three types of attributes: – Composite attribute • Made up of one or more atomic attributes • Name (First, Middle, Last Name) • Address (Street, City, PostalCode, Country) • Always split composite attributes into atomic attributes for searching reasons – Derived attribute • Attribute whose value can be derived from other atomic attribute values (age) 1-45 Docsity.com Entities and Attributes (cont’d) FIGURE I-12 ER model entity and attributes 7— Primary key Entity —| UniversityStudent PK | StudentID —— StudentName + Attributes StudentDOB — StudentAge — | — Atomic attribute -— Derived attribute Composite attribute comprised of StudentFirstName, StudentMiddlelnitial, and StudentLastName 1-46 Docsity.com Relationships • Represent associations between entities • Shown as a line between two entities 1-47 Docsity.com Relationship Cardinality (cont’d) • Maximum cardinality – Specifies the maximum number of entity instances with which a related entity instance can be associated – Three possible maximum cardinality combinations are: • One to one • One to many • Many to many 1-50 Docsity.com Relationships FIGURE |-13 ER model relationships UniversityInstructor ServiceProject PK | InstructorID PK | ProjectID InstructorLastName ProjectDescription InstructorFirstName ProjectStartDate InstructorOffice | ——— One-to-one One-to-many —— Advises Completes UniversityStudent UniversityCourse PK | StudentID PK | CourselD StudentLastName =P Enrolisin—9 CourseName StudentFirstName CourseTitle StudentMI StudentDOB Many-to-many 1-51 Docsity.com Relationships FIGURE |-14 ER model relationship showing minimum cardinalities Universitylnstructor ServiceProject PK | InstructorID PK | ProjectID InstructorLastName ProjectDescription InstructorFirstName ProjectStartDate InstructorOffice Advises © -———— Completes _ fj UniversityStudent UniversityCourse PK | StudentID PK | CourselD StudentLastName PO Enrollsin—o< CourseName StudentFirstName CourseTitle StudentMI StudentDOB 1-52 Docsity.com Relationship Attributes FIGURE I-16 Creating a new entity in place of a relationship with an attribute UniversityStudent UniversityCourse PK | StudentID PK | CourselD »O— Enrollsin —o< StudentLastName CourseName StudentFirstName EnrollmentGrade CourseTitle StudentMI StudentDOB UniversityStudent CourseEnrollment UniversityCourse PK | StudentID PK | EnrollmentID PK | CourselD StudentLastName [Has —-O€ EnrolimentGrade 9 PO Has 44 CourseName StudentFirstName CourseTitle StudentMI StudentDOB Docs. ity.com Creating ER Models • A three-step process: – Use data dictionary output from database analysis phase to identify all data entities and their associated attributes – Define a primary key for each entity – Create relationships and define cardinalities based on the organization’s business rules 1-56 Docsity.com Creating ER Models (cont’d) • Identifying entities and attributes – Is it an entity or an attribute? – Entity • Multiple entity instances exist in the database • Developer is not sure how many instances will exist • UniversityStudent – Attribute • A data item has a single value for each entity instance • FirstName, LastName, StudentNumber, Photo 1-57 Docsity.com Normalization • Transformation of the logical data design specified by the ER model into the tables that will be created within the database so the database tables do not have inconsistencies and do not store redundant data 1-60 Docsity.com Using an ER Model to Define Relational Database Tables • Create a database table to represent each entity • Designate the entity’s primary key as the table’s primary key • Specify a field to represent each attribute – Specify data type – Specify if the value can be NULL • Represent relationships using foreign keys – Identify whether foreign key values can be NULL 1-61 Docsity.com Using an ER Model to Define Relational Database Tables 1-62 Docsity.com Using an ER Model to Define Relational Database Tables (cont’d) 1-65 Docsity.com One-to-Many • Place the primary key of the entity on the “one” side of the relationship as a foreign key in the table that represents the entity on the “many” side of the relationship • If minimum cardinality of the “many side” of the relationship = 0, the foreign key value can be NULL • If minimum cardinality of the “many side” of the relationship = 1, the foreign key value cannot be NULL 1-66 Docsity.com Many-to-Many • Create a linking table (bridge table), which represents a unique combination of instances of the two relationship entities • Primary key of a linking table is usually a composite key made up of the primary keys of both of the relationship entities • Composite key values are foreign keys 1-67 Docsity.com Assignment 1 * Comments on Assign 1 1-70 Docsity.com Learning Objectives • To describe different database architectures • Recognize the server and client components of a SQL Server database • Understand SQL Server database objects 1-71 Docsity.com Database System Architectures • DBMS – Program that performs basic data handling procedures • The most common database system architectures – Single-tier – Two-tier – N-tier 1-72 Docsity.com Single-Tier Database Systems (cont’d) • Advantages – Users at separate locations can share data – Easy to deploy new database applications to users – A single point for maintaining security – Easy backup and data recovery in the event of a system failure 1-75 Docsity.com Two-Tier Database Systems • Also called client-server database system – DBMS runs on a server – Database applications run on client workstations – Clients and server are connected through a network 1-76 Docsity.com Two-Tier Database Systems Database server Client workstations Network Database , [| } Applications ; Database , } DBM Applications nate Database » | | } Applications ==, 1-77 Docsity.com Two-Tier Database Systems (cont’d) • Disadvantages – Handling individual client workstations • Installation of large and complex database applications • Upgrading the application as the business rule changes 1-80 Docsity.com N-Tier Database Systems • Distributed database application processing among multiple computers • Provides Web-based interfaces – Enable users to view and modify data using a Web browser • A user needs: – Network connection – Browser 1-81 Docsity.com N-Tier Database Systems Database server : | Network Middle-tier server(s) Business Services Client workstations Network User Services User [<———> : Services User Services 1-82 Docsity.com SQL Server Database Objects • Database Object – Set of logically related tables and programs that belong to a specific database application – Referred to as a database – All the tables for a database system is in a single database • DBMS – Manages multiple databases – The number of database management depends on • Server’s hardware configuration • Size of database 1-85 Docsity.com SQL Server Database Objects (cont’d) • A large system is divided into smaller databases – Simplifies file backup and management – Facilitates specifying user security permissions • System automatically creates multiple system databases when SQL Server DBMS is installed 1-86 Docsity.com Creating and Managing Databases • Query – Text command used to interact with a database • SQL (Structured Query Language) – Standard query language of relational databases • T-SQL(Transact-SQL) – Microsoft’s extended SQL dialect for the SQL Server DBMS • The American National Standards Institute (ANSI) oversees standards for SQL 1-87 Docsity.com