 |
|
|
| Lecture One Relational Database Concepts
|
Lecture Menu |
||
|
· Every organization has information needs. A library maintains a list of members, books, due dates, and fines. · A company maintains information about employees, departments, and salaries. These pieces of information are called data. · Organizations can store data on various media and in different formats. For example, a hard-copy document can be stored in a filing cabinet, or data can be stored in an electronic spreadsheet or in a database. · A database is an organized collection of information. The benefit of storing information in a database is to make data easy to access and manage. · To manage a database efficiently, you need a database management system (DBMS). · A DBMS is a system that stores, modifies, and retrieves data in the database on request. · There are four main types of databases: hierarchical, network, relational, and more recently, object relational. · Before 1970, hierarchical and network data structures were the more popular models for information storage. These models depend on the structure of the data and the way information is linked in the hierarchy or network. · Motivated by dissatisfaction with the cumbersome characteristics of hierarchical flat-file databases, E. F. Codd, a computer scientist working for IBM in the 1950s, developed an alternative for the hierarchies model: the relational model. · Instead of storing data in hierarchies, Codd proposed storing related data items, such as control numbers and ordered products, in tables. If the tables were designed according to a few simple principles, they were both intuitive and extremely efficient in storing data, as Codd discovered. · A single data item could be stored in only one place. Over time, many software makers recognized the significance of Codd's work and began developing products that adhered to Codd's model. · Since the 1980s, virtually all database software products (including Oracle's) conform to the relational model. We will focus on relational and object relational databases. |
|||
|
· A relational database is a collection of relations or two-dimensional tables.
· A relational database uses these two-dimensional tables to store information. The relational database model consists of three components: a collection of objects or relations, a set of operators, and data integrity rules. · Tables are the objects in the relational database model. · Central to the success of the relational model is the use of a relational database management system, or RDBMS, for storing, retrieving, and manipulating data in a database. · Earlier products required organizations to have many COBOL programmers on staff to code mechanisms for managing data-retrieval routines that interact directly with the files of the database. · In contrast, the RDBMS handles these tasks automatically using a functional programming language called SQL (pronounced either sequel or as the letters spelled out). SQL stands for structured query language, and it allows users to request the data they want according to strict comparison criteria. |
|||
|
· For example, if we wanted to look at an employee ID number and salary information for an employee named SMITH, the following code block shows a SQL statement that would help us do so: SQL> SELECT EMPNO, ENAME, SAL FROM EMP · The relational model needs data integrity rules. Integrity rules are laws that govern the operations allowed on data in a database. Integrity rules ensure that data is consistent and accurate. · A user who wants to access data in the relational database executes SQL statements to send requests to the database for information. · Users do not need to know the physical arrangement of data in a relational database and can retrieve information in any order they specify. · For example, managers can retrieve data listing their employees in alphabetical order, and accountants can retrieve the same data in order of salary. Note: The RDBMS retrieves and defines required information in response to the users requests by means of SQL. |
|||
|
· A relational database can contain one or many tables. · Tables make up the basic storage structure of a relational database management system. · A table holds the data about something in the real world, such as employees, departments, or salaries. · For example, the EMP table contains information about employees. Categories of information in the EMP table might include employee number, last name, job title, manager number, hire date, monthly salary, commission, and department number. · A single row, or record, represents all data for a particular employee. The order of rows stored in a table is insignificant. You can specify an order when you retrieve data from a table.
· A column, or attribute, represents all data regarding one specific characteristic about employees.
· The EMPNO column represents a primary key column for the EMP table. Each row must contain a value in this column, and the value cannot be a duplicate. Therefore, a primary key value uniquely identifies the row in a table. You cannot usually modify a primary key value. · In the EMP table, the JOB column represents a column that is not a key value. Values do not have to be unique or refer to unique values in another table. · A field is the point of intersection of a row and a column. There can be only one value in a field. · A field can have no value in it. This is called a null value. · Example: The COMM column in the EMP table contains information regarding commissions earned by sales people. A field in the COMM column contains a value only if the job field contains the value SALESMAN.
· Otherwise, the field in the COMM column contains a null value. A primary key column cannot contain any null values. |
|||
What an ORDBMS Is· As object-oriented programming has gained popularity, Oracle has adjusted its relational database-management paradigm to include support for object-relational database design. · This methodology incorporates the best features of object programming with the best features of relational programming and allows the developer to draw from both when designing a system in Oracle. · Two important features supported on the object side include: · Permitting users to define the structure of the data they wish to store · Allowing users to define programmatic methods for manipulating that data and associating those methods directly to the data stored |
|||
Application Development Cycle |
|||
The Logical Model· In the design phase of the system development cycle, a logical model of the database is created. A logical model of an RDBMS is typically a block diagram of entities and relationships, referred to as an ER diagram. · An ER model has · Entities · Relationships · Attributes An ER model is visual, showing the structure, characteristics, and interactions within and around the data being modeled. Entities and Attributes: · An entity in a logical model is much like a noun in grammar: a person, place, or thing. The characteristics of an entity are known as its attributes. An attribute is detailed information about an entity that qualifies, identifies, classifies, or quantifies the entity. · Consider this example: ABC Inc. has many offices in the US; each office has many departments, and each department may have many employees. Looking at ABC Inc. in terms of the ER model, you could identify OFFICE, DEPARTMENT, and EMPLOYEE as entities. · Each entity will also have its own characteristics; for instance, when you say "office," you might want to know the city and state where the office is located, as well as how many employees work there. · Similarly, you might want to know the department's name, its head, and an employee's name and date of birth. You might also like to know the name of the employee's spouse. In the following Figure, OFFICE, DEPARTMENT, and EMPLOYEE are entities, and their attributes are inside the box under each entity. · There are optional and mandatory attributes. In the following figure for example, under EMPLOYEE, the spouse name is optional, whereas the employee name, department, and date of birth are mandatory. An asterisk along with the attribute name indicates that it is mandatory. The optional attribute may be indicated by a small letter o. Relationships and Unique Identifiers: · In the example of ABC Inc., each office has many departments and each department has many employees. This describes the relationship between the entities. If there is an office in one city, there should be at least one department. · So, it is mandatory to have at least one occurrence of department for each location, although there may be many departments. In the ER model, a solid line represents a mandatory relationship and a crowfoot represents the "many." In a department, however, there may not be any employees at all. Optional occurrence is represented by a dotted line. · You should be able to identify each occurrence of an entity uniquely. What happens if there are two employees with the same name—how do you distinguish between them? · For office location, the city and state uniquely identify each office; for department, the department name identifies it uniquely. For employee, we can introduce a unique identifier (UID) called employee number. UID is represented in the diagram using a pound symbol (#). · As you can see in following figure, three types of relationships can be defined between entities. One-to-One: A one-to-one relationship is one in which each occurrence of one entity is represented by single occurrence in another entity. For example, consider an individual and that individual's Social Security number: One person can have only one Social Security number, and a Social Security number can belong to only one person. One-to-Many: A one-to-many relationship is one in which an occurrence of one entity may be represented by many occurrences in another entity. An example is department and employees: One department has one or more employees, and an employee belongs to only one department. Many-to-Many: A many-to-many relationship is one in which an occurrence from one entity may be represented by one or more occurrences in another entity, and an occurrence from the second entity may be represented by one or more occurrences in the first entity. The relationship between doctor and patient is an example: A patient can visit many doctors, and a doctor can have many patients. Note: Many-to-many relationships should not exist in RDBMS because they cannot be properly joined to represent a single row correctly. To solve this, create another entity that has a one-to-many relationship with the first entity and one-to-many relationship with the second entity. |
|||
|
· You have learned about RDBMS and how Oracle implements RDBMS using a variety of objects and structures. The Entity-relationship diagram is a modeling tool used in the beginning stages of application development. · The stages of the system development cycle are analysis, design, development, testing, and implementation. · Data in the Oracle database is managed and accessed using SQL. A SELECT statement is used to query data from a table or view. You can limit the rows selected by using a WHERE clause and order the retrieved data using the ORDER BY clause. · SQL*Plus is Oracle's native tool to interact with the database. SQL*Plus supports all SQL commands and has its own formatting and enhancement commands. |
|||
|
1. Look at the following diagram. What kind of relationship exists between Movies and Characters?
A. Each movie may have one or more characters. B. Each movie must have one or more characters. C. Many movies may have many characters. D. One movie can have only one character. 2. You are exploring theoretical aspects of the Oracle RDBMS. Which of the following choices identifies an aspect of data management that the Oracle RDBMS does not handle on your behalf? A. Datatype conversion B. Disk reads C. Sorting and formatting return data D. Defining required information via SQL 3. You are evaluating the use of Oracle to replace legacy pre-relational systems in your organization. In comparison to the Oracle RDBMS, which of the following aspects of pre-relational database systems did those systems handle as well as their relational counterpart? A. Many-to-many
data relationships C. Adaptability to changing business needs D. Data manipulation 4. What is the name of the scientist who first conceptualized the use of relational database management systems? _______________________ 5. What is SQL? 6. What is SQL capable of? Answer Key 1.B 2. D. 3.B. 4. E. F.Codd. |
|||
|
1. What does a single line with a crowfoot on one end represent entity-relationship diagram? A. One-to-one relationship B. One-to-many relationship C. Many-to-many relationship D. An access path 2. When designing the physical model from the logical model, which element from the ER diagram may be attributed as a table? A. Relationship B. Attribute C. Unique identifier D. Entity 3- A DBMS is: A. a single table of information, similar to an electronic spreadsheet. B. an organized collection of information. C. a system that stores, retrieves, and modifies data. D. a data structure dependent on the hierarchical linking of data. 4- In a relational database, the order of rows is: A. always significant. B. insignificant. C. important in relation to column order. D. significant only when querying the database. 5- A field with no value in it is called A. a primary key value. B. a comm. Value. C. a null value D. a foreign key value Answer: 1-b 2-d 3-c 4-b 5-c |
|||
| Required Readings: Textbook: Chapter 1 “Basic SQL SELECT Statements” of “Introduction to Oracle9i SQL”, Chip Dawes and Biju Thomas, Sybex. ISBN 0-7821-4062-9. ORTextbook: Chapter 1 “Retational technology and Simple SQL Select Statements” of “OCP: Oracle8i DBA and PL/SQL Study Guide”, Chip Dawes and Biju Thomas, Sybex. ISBN 0-7821-2682-0. |
|||
