Database Management Systems using SQL, MySQL (Computer Project)

Get this Project:

Fields with * are mandatory

INTRODUCTION

Data and information. These two things became leading factors through the past 50 years and during the 20th and 21th century as these concepts play a significant part of our everyday life. As in our society the role of the information is being valorised, we are getting more and more pieces of information. We are continuously bombed with information from the outside world: we get the news from the television, radio, and newspapers and we are being informed about the latest happenings from the fellow human beings all day long.

Of course we try to sort out and concentrate on the most important ones from the quarry of information. It is especially relevant as it seems to be impossible to memorize all the pieces of information. Sometimes we simply can’t memorize or wouldn’t like to memorize them. Accordingly we have to find another way of recording information instead of keeping them in mind. As the old Latin tag has it-verba volant, scripta manent–spoken words fly away, written ones remain.  In addition everybody has a share in reaching the recorded information in a fast and easy way.

BASIC ELEMENTS

p-20658--database-management-systems-

Information is the experienced, sensed, and understood data which is useful and new for the taker, who construes it according to their previous store of learning. Data means the appearance of a fact which we can record, store, modify, and send on. The conception of data is not an exact idea. In the point of view of database designing, data is the meaningless series of signs from which we can earn information after processing.

p-20658--database-management-systems-1

A database is the whole bulk of integrated and logically connected pieces of information; the system of data and connections between them; stored abreast. To be able to work with our database effectively deliberate designing is essential. The concept of database system consists of the databases, the computer resources, moreover, in a wider sense, the database-administrators, who are the ones who put through the designing and programming of the database.

p-20658--database-management-systems-2

Of course, the world has developed. There is cable so we can connect any number of computers. But the problem was that the database management was young at that time. So, they have developed this wonderful file – server architecture. I have to mention it in parentheses that although the use of the Novell server is not exclusive, but its best time was then. That hasn’t been so long, about 15 years. But in the information technology that had been a long time. They were worked out very well. They were robust systems, but “file-server”. It is already in its name that it is for to share documents and files as source of energy.

IMPROVEMENT OF DATA MODELS

p-20658--database-management-systems-4

This one is the most ancient data model. Datas are stored in a hierarchical structure which is similar to a tree. Every intersection of the tree refers to one type of record. There is parent-children relationship between the datas. Every data can have infinite number of children but only one parent. This model can be used to one-to-one and one-to-many relationships as well. Lately this model has been absolutely displaced by the relational model.

p-20658--database-management-systems-3

Many-to-many relations rely on one-to-many relations. From any individual point of view we can discover an one-to-many relationship. Therefore every many-to-many relationship can be split to two one-to-many relationship. So far we talked about such relations which could be made between two individuals. These are so-called binary relations.

DATABASE PLANNING AND ITS CONTRIVANCES

p-20658--database-management-systems-5

The very first step of database designing is that we have to know what type of database management system we use. The use of Access database management system goes with subdividing data into groups, taking the items being close to each other into one table, then specifying the relations between the tables – just like in relational database management systems. So designing and creating a database is a quite complicated job and it requires some creativity as well.

LANGUAGE REFERENCE OF SQL

The first extent gets 100K memory, the second – 50K. By the PCINCREASE 20 values of every extent grow with 20% compared to the previous. MINEXTENTS 1 MEANS, the table gets in first time 1extent, the MAXEXTENTS 99 – the table get 99 extent maximum. The tables may be partitioned as like the index tables. The partitioning gives the possibility of the very large tables and index tables for the treatment of.

During the partitioning of the large table, the system divides into that to smaller and it more manageable. Partitioned table – This is a table where, the rows are smaller but identical construction with boards, partitions, they share. The partitions can be stored on different physical locations. The benefits of partitioning:

  • Better distribution of I/O-load
  • Backup and restore is a simple.
  • Minimize the possibility of data corruption.
  • Help to the archiving process.

VIEWS AND INDEXES

p-20658--database-management-systems-6

Let’s put the case that we would like to store the record having the k1 type key. Firstly we have to find the block in which the record must be stored. If it were in the data file, let’s name it Bi block. After that two cases can exist. It depends on whether there is enough space for the k1 key in the Bi block or not. If there is, we don’t have much to do only to write it into the Bi block as a record. If there is no space, we’ll have to make. We have to make a new empty block.

CONSTRAINTS, INTEGRITY RULES, TRIGGERS

The constraints are such regulations we can provide our expectations related to the content of the database. When we make these statements for the database system in one place once, it will make sure to force them. If we happen to design them in the user-interface, we would modify and declare them in many places. Constraints are checked after every action which would change the content of the database in a way that the content would not satisfy the constraints. Constraints are valid since we declare them. They do not have a retrospective effect. Execute a delayed check by the keyword DEFFERRED.

TRIGGERS: (ORACLE 10g)

The trigger defines an activity which executed automatically when a table or a view is being modified or other user- or system events should occur. So, any change in the database starts a trigger. The trigger is a database object. Triggers are working transparent from the angle of the user.

Triggers could triggered by:

  • An INSERT, DELETE or an UPDATE statement executed on a table or a view
  • Some DLL statements
  • Server errors
  • User logon or logoff
  • Starting or stopping a database

We use them in the following cases:

  • generating inherited column value
  • prevent an invalid transaction
  • protection
  • defining referential integrity constraints
  • handling complex business rules
  • event logging
  • trace
  • collect table statistics
  • multiplying data

BASICS OF PL/SQL

Every database table has a pseudo column named ROWID which stores a binary value the row identifier. Every row identifier based on its storage address. The physical row identifier identifies “normal table” the logical row identifier an associative arrays. The ROWID datatype’s domain there are physical identifiers. On the other hand the UROWID data type can handle physical, logical, and foreign (non-Oracle) row identifiers.

Author: Tibor Radványi

Download Project

>> List of Student DBMS Project Ideas using SQL, MySQL for B.E/B.TECH Students

Get this Project:

Fields with * are mandatory

Leave a Comment

Your email address will not be published. Required fields are marked *