Database Notes

 

 

 

Database Notes

 

The following texts are the property of their respective authors and we thank them for giving us the opportunity to share for free to students, teachers and users of the Web their texts will used only for illustrative educational and scientific purposes only.

 

All the information in our site are given for nonprofit educational purposes

The information of medicine and health contained in the site are of a general nature and purpose which is purely informative and for this reason may not replace in any case, the council of a doctor or a qualified entity legally to the profession.

 

 

Database Notes

 

Database Notes

Degree: Degree of a table is total number of attributes in a relation.

 

Cardinality: Cardinality of a table is total number of rows(tuple) in a relation.

 

Primary Key: An attribute or set of attributes which are used to uniquely identify a tuple is known as Primary Key.

 

Candidate Key: If a table has more than one such attributes which identify a tuple uniquely than all such attributes are known as candidate Keys.

 

Relation: A relation is a table having atomic values, unique rows and columns. i.e. Data arranged in rows and columns and having certain properties.

 

Foreign key: Non-key attribute , whose values are derived from the primary key of some other table, is known as foreign key in its current table..

 

Attribute: A column(field) of a relation is termed as an attribute.

 

Alternate Key: A candidate key that is not the primary key is termed as alternate key.

 

Database: It is a collection of interrelated data.

 

Domain: It is a pool of values from which the actual values appearing in a given column are drawn.

 

Tuple: The  rows of tables (relations) are generally referred to as tuples.

 

DBMS(Database Management System): It is basically a computer based record keeping system.

The advantages provided by a database system are:

• Reduced data redundancy
• Controlled data inconsistency
• Shared data
• Standardized data
• Secured data
• Integrated data

 

Different types of users that work on database systems:

A primary goal of database system is to provide an environment for retrieving information from and storing new information into the database. There are three different types of database system users, differentiated by the way they expect to interact with the system.

• End user: An end user is a person who is not a computer trained person but uses the database to retrieve some information
• Application system Analyst: This user is concerned about all of the database at logical level i.e. what all data constitutes the database? What are the relationships between the data entities etc. without considering the physical implementation details.
• Physical storage System Analyst: This user is concerned with the physical implementation details of the database such as which storage device? Which storage technique should be used?

 

Various levels of Data abstraction in a database system:

There are 3 levels of abstraction:

• Internal level(Physical level): This level describes how the data is actually stored on the storage medium. At this level, complex low level data structures are described in details.
• Conceptual level: This level describe what data are actually stored in the database. It also describes the relationships existing among data. At this level, the database is described logically in terms of simple data structures.
• External level(View level): This level is concerned with the way the data is viewed by individual users. Only a part of the database relevant to the user(s) is provided to them through this level.

 

Data Independence: The ability to modify a scheme definition in one level without affecting a scheme definition in the next higher level is called Data Independence.

There are two levels of data independence.

 

Physical Data Independence: refers to the ability to modify the scheme followed at the physical level without affecting the scheme followed at the conceptual level. That is the application programs remain the same even though the scheme at physical level gets modified (to improve performance of the system).

 

Logical Data Independence: refers to the ability to modify the conceptual scheme without causing any changes in the schemes followed at view levels. Modifications are necessary whenever logical structures of the database get altered because of some unavoidable reasons(for e.g. adding one field in the table)

It is more difficult to achieve logical data independence than the physical data independence. The reason being that the application programs are heavily dependent on the logical structure of the database.

 

Relation Algebra: It is a collection of operations on relations. Operations include select, project, cartesian product, union, se difference, set intersection, natural join, division etc. Select and project are unary operations since they operate on one relation. The others are binary operations.

 

• Select Operation: It selects tuples (horizontal subset) from a relation that satisfy a given condition. Denoted by symbol s e.g.        s price > 14.00 (items)

From table items, select the tuples satisfying the condition price >14.00.

 

(b) Project Operation: It yields a vertical subset of a given relation. It lets you select specified attributes in a specified order. The result is also a relation, the duplicating tuples are automatically removed. It is denoted by p.                         e.g  p supp_name , city          (suppliers)

from table suppliers project the attributes supp_name and city

 

(c) Cartesian Product: It is a binary operation and is denoted by x. The Cartesian product of two relations A and B is A x B, it yields a new relation which has a degree (no. of attributes) equal to the sum of the degrees of the two relations operated upon. The no. of tuples(cardinality) of the new relation is the product of the number of tuples of the two relations operated upon. The cartesian product of two relations yields a relation with all possible combinations of the tuples of the two relations operated upon.

 

(d) Union Operation: It is a binary operation that requires two operands. It produces the third relation that contains tuples from both the operand relations. It is denoted by  È . For union to be valid, the following two conditions must be satisfied by the two operands A and B.

• The relations A and B must be of same degree. That is, they must have the same number of attributes.
• The domains of the ith attributes of A and the ith attribute of B must be the same.

 

(e) Set Difference Operation: It is denoted by symbol – (minus) allows us to find tuples that are in one relation but not in another. The expression A – B results in a relation containing those tuples in A but not in B.

 

(f) Set Intersection operation- It finds tuples that are common to the two operand relations. It is denoted by ∩. i.e A∩B will yield a relation having tuples common to A and B.

 

Various Data Models:

 

A data model is a collection of conceptual tools for describing data, data relationships, data semantics etc. There are generally three data models available: relational, network and hierarchical model.

 

• Relational Data Model: It represents data and relationships among data by a collection of tables known as relations, each of which has a number of columns with unique names.

 

• Network model: The network model represents data by collection of records and relationships among data are represented by links which can be viewed as pointers. The records in the database are organized as collection of arbitrary graphs.

 

• Hierarchical  model: It is similar to the network model in the sense that data and relationships among data are represented by records and links respectively. It differs from the network model in that the records are organized as collection of trees rather than arbitrary graphs. The relational model differs from the network and hierarchical models in that it does not use pointers or links. Instead, the relational model relates by the values they contain.

 

Views

A view is a virtual table that does not really exist in its own right but is instead derived from one or more underlying base table(s). The view is a kind of table whose contents are taken upon other tables depending upon a given query condition. No stored file is created to store the contents of a view rather its definition is stored only.

 

Use of View : It gives a way to people to access some not all of the information in a table.

 

DDL(Data Definition Language) It provides commands for defining relation schemas, deleting relations, creating indexes and modifying relation schemas. E.g Create Table, Drop table

 

DML(Data manipulation language) It includes a query language to insert, delete and modify tuples in the database and perform complex queries on these tables.

.

Difference between DDL and DML:  DML is used to put values and manipulate them in tables and other database objects and DDL is used to create tables and other database objects.

 

Difference between Drop table and Drop View:  Drop Table removes a table from a database. Drop view removes a view from the database but table remains.

 

Difference between WHERE and HAVING clause: The HAVING clause places conditions on groups in contrast to WHERE clause, which places conditions on individual rows. In Having clause we can use aggregate functions whereas in WHERE clause we cannot use aggregate functions.

 

Source : http://pujamcsdwarka.wikispaces.com/file/view/Database+Notes.doc/121192077/Database%20Notes.doc

Web site link: http://pujamcsdwarka.wikispaces.com/

Google key word : Database Notes file type : doc

Author : not indicated on the source document of the above text

If you are the author of the text above and you not agree to share your knowledge for teaching, research, scholarship (for fair use as indicated in the United States copyrigh low) please send us an e-mail and we will remove your text quickly.

 

Database Notes

 

If you want to quickly find the pages about a particular topic as Database Notes use the following search engine:

 

 

 

Database Notes

 

Please visit our home page

 

Larapedia.com Terms of service and privacy page