Relational Databases




A relational database is based on the relational model and uses a collection of
tables to represent both data and the relationships among those data. It also includes
a DML and DDL. 


1. Tables


Each table has multiple columns and each column has a unique name. Figure 1.2
presents a sample relational database comprising two tables: one shows details
of university instructors and the other shows details of the various university
departments.


The first table, the instructor table, shows, for example, that an instructor
named Einstein with ID 22222 is a member of the Physics department and has an
annual salary of $95,000. The second table, department, shows, for example, that
the Biology department is located in the Watson building and has a budget of
$90,000. Of course, a real-world university would have many more departments
and instructors. We use small tables in the text to illustrate concepts. A larger
example for the same schema is available online.

Topics You May Be Interested In
Characteristics Of The Database Approach Sql Data Definition
What Is Database Management System Additional Basic Operations-the Rename Operation
Specialty Databases Weak Entity Types
History Of Database Systems Relationship Between Information Security Versus Information Privacy
Relational Operations Aggregate Functions


The relational model is an example of a record-based model. Record-based
models are so named because the database is structured in fixed-format records
of several types. Each table contains records of a particular type. Each record type
defines a fixed number of fields, or attributes. The columns of the table correspond
to the attributes of the record type.


It is not hard to see how tables may be stored in files. For instance, a special
character (such as a comma) may be used to delimit the different attributes of a
record, and another special character (such as a new-line character) may be used
to delimit records. The relational model hides such low-level implementation
details from database developers and users

.
We also note that it is possible to create schemas in the relational model that
have problems such as unnecessarily duplicated information. For example, suppose
we store the department budget as an attribute of the instructor record. Then,
whenever the value of a particular budget (say that one for the Physics department)
changes, that change must to be reflected in the records of all instructors
associated with the Physics department.

Relational Databases

Topics You May Be Interested In
Modeling Of Union Types Using Categories Introduction To Database Security Issues
Example Of Other Notation: Representing Specialization And Generalization In Uml Class Diagrams Xml: Extensible Markup Language
Using High-level Conceptual Data Models For Database Design Entity Types, Entity Sets, Attributes, And Keys-1
Database Languages Sensitive Data And Types Of Disclosures
History Of Database Systems Database Security And The Dba

2. Data-Manipulation Language


The SQL query language is nonprocedural. A query takes as input several tables
(possibly only one) and always returns a single table. Here is an example of an
SQL query that finds the names of all instructors in the History department:


select instructor.name
from instructor
where instructor.dept_name = ’History’;


The query specifies that those rows from the table instructor where the dept_name is
History must be retrieved, and the name attribute of these rows must be displayed.
More specifically, the result of executing this query is a table with a single column
labeled name, and a set of rows, each of which contains the name of an instructor
whose dept_name, is History. If the query is run on the table in Figure 1.2, the result
will consist of two rows, one with the name El Said and the other with the name
Califieri.

Topics You May Be Interested In
Subclasses, Superclasses, And Inheritance Database Schema
Constraints And Characteristics Of Specialization And Generalization Hierarchies Overview Of Object Database Concepts
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Introduction To Transaction Processing Relationship Types Of Degree Higher Than Two
Relational Databases Access Control, User Accounts, And Database Audits


Queries may involve information from more than one table. For instance, the
following query finds the instructor ID and department name of all instructors
associated with a department with budget of greater than $95,000.


select instructor.ID, department.dept name
from instructor, department
where instructor.dept name= department.dept name and
department.budget > 95000;


If the above query were run on the tables in Figure 1.2, the system would find
that there are two departments with budget of greater than $95,000—Computer
Science and Finance; there are five instructors in these departments. Thus, the
result will consist of a table with two columns (ID, dept name) and five rows:
(12121, Finance), (45565, Computer Science), (10101, Computer Science), (83821,
Computer Science), and (76543, Finance).


3. Data-Definition Language

Topics You May Be Interested In
Constraints And Characteristics Of Specialization And Generalization Hierarchies Discretionary Access Control Based On Granting And Revoking Privileges
Using High-level Conceptual Data Models For Database Design Introduction To Database Security Issues
Purpose Of Database Systems The Natural Join
Database Languages Additional Basic Operations-the Rename Operation
Database Design Null Values


SQL provides a rich DDL that allows one to define tables, integrity constraints,
assertions, etc.


For instance, the following SQL DDL statement defines the department table:


create table department
  (dept_name   
char (20),
  building       
 char (15),
  budget   
      numeric (12,2));


Execution of the above DDL statement creates the department table with three
columns: dept name, building, and budget, each of which has a specific data type
associated with it. In addition,the DDL statement updates the data dictionary, which
contains metadata . The schema of a table is an example of metadata.

Topics You May Be Interested In
View Of Data Database Architecture
Data Abstraction, Knowledge Representation, And Ontology Concepts Data Mining And Information Retrieval
Database Security Introduction To Database Security Issues
Database Languages Relational Operations
Data Storage And Querying Basic Structure Of Sql Queries

4. Database Access from Application Programs


SQL is not as powerful as a universal Turing machine; that is, there are some
computations that are possible using a general-purpose programming language
but are not possible using SQL. SQL also does not support actions such as input
from users, output to displays, or communication over the network. Such computations
and actions must be written in a host language, such as C, C++, or Java,
with embedded SQL queries that access the data in the database. Application
programs
are programs that are used to interact with the database in this fashion.
Examples in a university system are programs that allow students to register for
courses, generate class rosters, calculate student GPA, generate payroll checks, etc.


To access the database, DML statements need to be executed from the host
language. There are two ways to do this:


• By providing an application program interface (set of procedures) that can
be used to send DML and DDL statements to the database and retrieve the
results.
          The Open Database Connectivity (ODBC) standard for use with the C
language is a commonly used application program interface standard. The
Java Database Connectivity (JDBC) standard provides corresponding features
to the Java language.

Topics You May Be Interested In
Using High-level Conceptual Data Models For Database Design Discretionary Access Control Based On Granting And Revoking Privileges
Introduction To Transaction Processing Xml: Extensible Markup Language
Database Languages The Natural Join
Relational Databases Entity Types, Entity Sets, Keys, And Value Sets-2
Structure Of Relational Databases Access Control, User Accounts, And Database Audits


• By extending the host language syntax to embed DML calls within the host
language program. Usually, a special character prefaces DML calls, and a
preprocessor, called the DML precompiler, converts the DML statements to
normal procedure calls in the host language.



Frequently Asked Questions

+
Ans: Database design mainly involves the design of the database schema. The design of a complete database application environment that meets the needs of the enterprise being modelled requires attention to a broader set of issues. view more..
+
Ans: A database system provides a data-definition language to specify the database schema and a data-manipulation language to express database queries and updates. view more..
+
Ans: This chapter discusses techniques for securing databases against a variety of threats. It also presents schemes of providing access privileges to authorized users. view more..
+
Ans: A relational database is based on the relational model and uses a collection of tables to represent both data and the relationships among those data. view more..
+
Ans: A relational database is based on the relational model and uses a collection of tables to represent both data and the relationships among those data. view more..
+
Ans: A relational database is based on the relational model and uses a collection of tables to represent both data and the relationships among those data. view more..
+
Ans: A database system is partitioned into modules that deal with each of the responsibilities of the overall system. The functional components of a database system can be broadly divided into the storage manager and the query processor components. view more..
+
Ans: A transaction is a collection of operations that performs a single logical function in a database application. view more..
+
Ans: The architecture of a database system is greatly influenced by the underlying computer system on which the database system runs. Database systems can be centralized, or client-server, where one server machine executes work on behalf of multiple client machines. view more..
+
Ans: The term data mining refers loosely to the process of semi-automatically analysing large databases to find useful patterns. view more..
+
Ans: Researchers have developed several data-models to deal with these application domains, including object-based data models and semi-structured data models. view more..
+
Ans: A primary goal of a database system is to retrieve information from and store new information into the database. People who work with a database can be categorized as database users or database administrators. view more..
+
Ans: Information processing drives the growth of computers, as it has from the earliest days of commercial computers. In fact, automation of data processing tasks predates computers. view more..
+
Ans: A relational database consists of a collection of tables, each of which is assigned a unique name. view more..
+
Ans: The database schema is the logical design of the database. view more..
+
Ans: A super-key is a set of one or more attributes that, taken collectively, allow us to identify uniquely a tuple in the relation. view more..
+
Ans: DBMS typically includes a database security and authorization subsystem that is responsible for ensuring the security of portions of a database against unauthorized access view more..
+
Ans: The typical method of enforcing discretionary access control in a database system is based on the granting and revoking of privileges. Let us consider privileges in the context of a relational DBMS. view more..




Rating - 3/5
514 views

Advertisements