Session 1 Summary. Session Objectives

1 Session 1 Summary Session Objectives 1.1 Define the term database and explain what is contained within a database in terms of schema and data. 1.2 I...
Author: Meagan Lamb
0 downloads 3 Views 469KB Size
1 Session 1 Summary Session Objectives 1.1 Define the term database and explain what is contained within a database in terms of schema and data. 1.2 Identify the term Database Management System (DBMS) and describe the main functions that a DBMS provides. 1.3 Summarize the history of database processing. 1.4 Recognize the purpose and use of a database. 1.5 Identify the concept of tables and relationships in a database. Database Systems Architecture

    

Users – interact with database applications Database applications – allow users to interact with the database Structured Query Language (SQL) – programming language used to define database schema and manage data. SQL is an ISO standard and compatible with all commercial database management systems. Database Management System –software that controls data storage, modification, and retrieval Database – contains schema and data

Database Structure 



Schema describes the logical structure of a database. The structure of a table with its associated attributes, data types, and constraints is an example of a schema object. Data Definition Language (DDL), a sub-category of SQL is used to create and manage database schema. Data are variables that are stored in a database. Data Manipulation Language (DML), a subcategory of SQL is used to query and manage data. Information is defined as knowledge derived from data, or as data presented in a meaningful context. Data is simply recorded in the database, but the data must be manipulated in some way to produce information.

2 Database Management System (DBMS) Functionality  Scalability – upper storage limits expressed in multiple petabytes of data  Multi-User Accessibility – able to accommodate millions of users  Data Persistency – stores data even after an application is closed  Reliability – fault tolerant data storage and point in time recovery options  Security – robust physical and logical security model  Ease of use – Delineation of logical design from physical implementation makes it easier to navigate – Declarative languages such as SQL allow a user to state what they want, and leave data retrieval strategies to the query optimizer  Optimized – performance can be fine tuned based on business needs Database Roles  Database Designer – develops the logical schema design  Data Administrator (DA) – management of the data  Database Administrator (DBA) – manages the database (storage, performance, security)  Application Developer – builds applications and processes that interface with the database  Database User – accesses database through a client application Three generations of Database Management Systems The CODASYL and hierarchical approaches represented the first generation of DBMSs. They were based on the concept that smaller components come together as parts of larger components, and so on, until the final product is assembled. This structure, which conforms to an upside down tree, is also known as a hierarchical structure. Relational DBMSs are referred to as second-generation DBMSs. In 1970, E. F. Codd of the IBM Research Laboratory produced his highly influential paper on the relational data model (“A relational model of data for large shared data banks,” Codd, 1970). This paper was very timely and addressed the disadvantages of the former approaches. Many experimental relational DBMSs were implemented thereafter. In response to the increasing complexity of database applications, two “new” systems have emerged: the object-oriented DBMS (OODBMS) and the object-relational DBMS (ORDBMS). However, unlike previous models, the actual composition of these models is not clear. This evolution represents third generation DBMSs. History of Database Processing    

1960s – traditional file-based storage 1970s – first generation; hierarchical and network databases – no longer used. 1980s – second generation; relational databases systems (RDBS), which is now the dominant system for business applications managing structured data – the focus of this class 1990s – third generation; object-oriented and object-relational databases (OODBS)

3 The relational database is the most commonly used type of database because they are much easier to design, implement and use than OODBMS. OODBMS products were not successful because (1) the costs of converting data stored in a relational format to an object-oriented format, and (2) the lack of a substantial advantage of OODBMS products over relational DBMS products for commercial data processing. Purpose and Use of Databases We are a technology-driven society and use databases throughout the day in both our personal and professional lives. Many of the everyday tasks you do are supported, behind the scenes, by databases. Think about how going to school is supported by at least one, if not many, databases. When you inquired about the Northwestern program, it is likely your name and information was placed in some sort of inquiry database. Upon applying and being accepted into the program, you were definitely entered into a database. It is through databases that you are allowed to register for class, obtain your grades, able to view your account information, and even pay your bill. Even your Blackboard classrooms are assigned and recorded using databases. Database Categories Category Personal database Workgroup database Department database Enterprise database Web-enabled database

Scope Single user located on PC Small team of people (< 25) working on an application Functional organizational unit < 100 people Entire organization for operations and decision-making Web browser access to applications and databases

Example Video/DVD rental store Several scientists performing drug research project A database used by Northwestern SCS HR department Enterprise resource planning (ERP) systems E-commerce companies to track customer behavior

Database Design Significant thought goes into the design of a database. The first step in the design of a database is to gather requirements for the database. Requirements typically come from key stakeholders in the database project and outline what is required of the database. For example, the type of information to be stored, updated, and accessed. Requirements documents can range from a few pages to several hundred pages, depending on the complexity. The requirements documentation outlines the entities in the database. An entity is simply a person or item represented in the database. For example, students, faculty, programs, and courses are some examples of entities that may be used in a university database. Each entity has attributes associated with it in the database as well. Attributes are the characteristics that describe a specific entity. For example, the student entity might have the following attributes: first name, last name, student ID, street address, apartment number, city, state, and zip code. Likewise, the entity of a course has attributes. The number and types of attributes as well as the number of entities is dependent upon the complexity of the database that is to be built. It would look something like the diagram below.

4 Student Student ID First name Last name Street address

Entities

Course Course code Course name Course description

Attributes

In addition, entities have relationships in a database. For example, an instructor teaches a course. This indicates that there is a relationship between the entity instructor and the entity course. Likewise a student enrolls in courses. Again, this indicates a relationship between the entity student and the entity course. There are three main types of relationship in a database: one-to-one, one-to-many, and many-tomany. To determine the type of relationship between entities in a database, examine the current “value” of each entity set. An entity set is simply the set of entities that belong to it. For example, we have identified the student entity. The entity set is the actual students that belong to this entity. Let’s give them some names so we can see how this might look. Students NetID Ak077 FS232 JB656 SB656 AH675

First_Name Faisal John Joe Sally Mike

Last_Name Akkawi johnson Brown Book Smith

Phone# 708 772 6323 312 555 6556 312 444 5454 324 545 5555 847 666 7676

Now let’s look at the entity course and provide a sample entity set as well. Courses Course_ID MSIS-DL 317 MSIS-DL 313

Course_Name Intro. To Database Telecommunication

Pre-requists none none

On-Line Yes no

In addition to looking at the “value” of the entity set to determine the type of relationship, you also need to examine the relationship. The value of a relationship is a relationship set. A key term to understanding a relationship set is the concept of “tuples”. A tuple is a mathematical term that represents the notion of an ordered list of elements. It is much like a data set, with two main differences—a tuple can contain an object more than once and has a finite size. Let’s look at an example of how this works. In our example, we know that students take courses. This means that there is a relationship between students and courses in the database. Now, the question is: What type of relationship is this? We call this the cardinality of the relationship. Cardinality is the number of times that one entity either can or must be associated with another entity. Typically, we focus on three main types of cardinality, or relationship types: one-to-one, one-to-many, or many-to-many. To determine this, we need to look at the value of both the entity set and the relationship set.

5 To do this, you can think of two columns of data. Student Joe Brown Sally Book Sam Cup

Course MSIS-DL 313 MSIS-DL 317 MSIS-DL 314 MSIS-DL 435

Now, we need to think through the relationship. Relationships between tables are expressed by identical data values stored in the associated columns of related tables in a relational database. Can our student Joe Brown, take more than one course? Yes, he can and so can other students. So, we can represent this in the tables as follows Student Joe Brown Joe Brown Sally Book Sally Book Sam Cup John Evening John Evening

Course MSIS-DL 313 MSIS-DL 317 MSIS-DL 313 MSIS-DL 317 MSIS-DL 314 MSIS-DL 317 MSIS-DL 435

As we can see from this expanded information, Joe, Sally, and John are taking two classes while Sam is taking only one. Let’s represent the data with another visual. Joe Brown

MSIS-DL 313

Sally Book

MSIS-DL 317

Sam Cup

MSIS-DL 314

John Evening

MSIS-DL 435

From this visual, we can see that this is a many-to-many relationship. A many-to-many relationship means that an entity of either set can be connected to many entities of the other set. In a one-to-many relationship, each entity occurrence of the second set is connected to, at most, one entity occurrence of the first set. But, an entity occurrence of the first set can be connected to zero, one, or many entity occurrences of the second set. One example is a database to record membership information for a club. The two tables will be MEMBER, which records information about the person who is a member of the club, and PAYMENT, which records annual dues, paid by the members over the years. The two tables to be created are: MEMBER (MemberNumber, MemberFirstName, MemberLastName, EmailAddress) PAYMENT (PaymentNumber, MemberNumber, PaymentDate, PaymentAmount) In a one-to-one relationship, each entity of either entity set is related to at most one entity occurrence of the other set.

6

Key Terms: a. Data. Stored representations of objects and events that have meaning and importance in the user’s environment like audio, video & text about objects. In other words, Data are facts and figures. b. Information. Is defined as knowledge derived from data, or as data presented in a meaningful context. Data is simply recorded in the database, but the data must be manipulated in some way to produce information. c. Metadata. Data that describes the properties or characteristics of end-user data and the context of that data. d. Entity A person, place, object, event, or concept in the user environment about which the organization wishes to maintain data. e. Database management system. A software system that is used to create, maintain, and provide controlled access to user databases. Examples of DBMS products are Microsoft (the SQL Server DBMS), Oracle Corporation (the Oracle DBMS), Sun Microsystems [purchased by Oracle] (the MySQL DBMS), and IBM (the DB2 DBMS). f.

Database. An organized collection of logically related data. In other words, a database is a collection of integrated tables, which means that the tables store both data and the relationships between the data. Examples of Database Systems (DBs):  A system that maintains component part details for a car manufacturer;  An advertising company keeping details of all clients and adverts placed with them;  A training company keeping course information and participants’ details;  An organization maintaining all sales order information.

g. Security. The protection of the database from unauthorized users, which may involve passwords and access restrictions. h. Integrity. The maintenance of the validity and consistency of the database by use of particular constraints that are applied to the data. i.

Views. These present only a subset of the database that is of particular interest to a user. Views can be customized, for example, field names may change, and they also provide a level of security preventing users from seeing certain data.

Suggest Documents