Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
This is a Lab Manual for Database Management Systems (DBMS)
Typology: Lab Reports
1 / 73
This page cannot be seen from the preview
Don't miss anything!
On special offer
The Entity-Relationship (E-R) data model perceives the real world as consisting of basic objects, called entities, and relationships among these objects. It was developed to facilitate database design by allowing specification of an enterprise schema, which represents the overall logical structure of a database. The E-R data model is one of several semantic data models; the semantic aspect of the model lies in its representation of the meaning of the data. The E-R model is very useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema. Because of this usefulness, many database-design tools draw on concepts from the E-R model. An entity is a “thing” or “object” in the real world that is distinguishable from all other objects. For example, each person in an enterprise is an entity. An entity has a set of properties, and the values for some set of properties may uniquely identify an entity. An entity is represented by a set of attributes. Attributes are descriptive properties possessed by each member of an entity set. The designation of an attribute for an entity set expresses that the database stores similar information concerning each entity in the entity set; however, each entity may have its own value for each attribute. Mapping cardinalities, or cardinality ratios, express the number of entities to which another entity can be associated via a relationship set. For a binary relationship set R between entity sets A and B, the mapping cardinality must be one of the following:
With the help of Entity-Relationship diagram we can create the required database and perform queries. For example, in case of Airline Reservation System we can make queries like
to find the schedule time of a flight, number of booked seats in a flight, flight fares etc. with the help of query language like SQL Relational Algebra, Relational Calculus etc. We can represent a database that conforms to an E-R database schema by a collection of tables. For each entity set and for each relationship set in database, there is a unique table to which we can assign the name of the corresponding entity set and relationship set. Each table has multiple columns, each of which has a unique name. Both the E-R model and the relational-database model are abstract, logical representations of real-world enterprises. Thus, we have studied how to construct an E-R diagram.
Data-definition language (DDL). The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas. The SQL standard supports a variety of built-in types, including:
of that attribute. For example, in Figure 3.1, the not null constraint on the name attribute of the instructor relation ensures that the name of an instructor cannot be null. DDL Commands :
important role as the give characteristics to the table and columns. Storing different types of data in table is only possible due to integrity contraints. Thus, we have studied the DDL Statements and Integrity Constraints.
SET column_name1 = newValue1, column_name2 = newValue2, ... column_nameN = newValueN WHERE condition;
Inserting data into table user INSERT INTO project2.user (UserID, FirstName, LastName, DOB, Contact) VALUES ('192160', 'Adam', 'Smith', '1998- 03 - 18', '9876543210'); INSERT INTO project2.user (UserID, FirstName, LastName, DOB, Contact) VALUES ('192161', 'Roger', 'Lewis', '1992- 11 - 24', '8182760123'); INSERT INTO project2.user (UserID, FirstName, LastName, DOB, Contact) VALUES ('192162', 'Angelika', 'Felix', '2001- 03 - 18', '9819487141'); INSERT INTO project2.user (UserID, FirstName, LastName, DOB, Contact) VALUES ('192163', 'Daniel', 'Blair', '1994- 07 - 01', '7210217434'); INSERT INTO project2.user (UserID, FirstName, LastName, DOB, Contact) VALUES ('192164', 'Mayson', 'Briggs', '1992- 09 - 13', '8898853976'); INSERT INTO project2.user (UserID, FirstName, LastName, DOB, Contact) VALUES ('192165', 'Will', 'Terner', '2000- 05 - 01', '9833834968'); Updating data into table user UPDATE project2.user SET FirstName = 'James' WHERE (UserID = '192164'); Deleting data from table user DELETE FROM project2.user WHERE (UserID = '192164');
Inserting data into table user Updating data into table user Deleting data from table user
A database is incomplete without the data in it. Storing data in the main part of the database. These data manipulation commands help us to do so. Insertion, updating and deletion are the basic purpose of an application and user data. These DML helps to simply manipulation of data. Thus, we studied the Data Manipulation Commands.
Data Controlling Language (DCL) helps users to retrieve and modify the data stored in the database with some specified queries. Grant and Revoke belong to these types of commands of the Data controlling Language. Granting of authorization for data access. By granting different types of authorization, the database administrator can regulate which parts of the database various users can access. The authorization information is kept in a special system structure that the database system consults whenever a user tries to access the data in the system. We may assign a user several forms of authorizations on parts of the database. Authorizations on data include:
U, then the privileges granted to all other users by user U will be revoked. To revoke an authorization, we use the revoke statement. It takes a form almost identical to that of grant: REVOKE <privilege-list> ON <relation-name or view-name> FROM <user/role-list>; Revocation of privileges is more complex if the user from whom the privilege is revoked has granted the privilege to another user.
Creating new user and setting privileges Root is the username of Local Instance. ‘->’ this symbol indicates that the query is executed by that User. GoTo Sever and then click Users and Privileges Created User1 and only SELECT privilege given to it. Creating new connection and using to access database.
Executing UPDATE query by User1: User 1 - > UPDATE game SET price= 1300 WHERE gameid= 9141 ; User 1 - > SELECT * FROM game; Creating another user User2 using SQL. root - > CREATE USER 'User2'@'localhost' IDENTIFIED BY 'User234@'; root - > GRANT ALL PRIVILEGES ON game TO 'User2'@'localhost'; User2 is given authorization to perform All queries. Creating connection of User
User2 - > UPDATE game SET price= 1400 WHERE gameid= 9141 ; root - > REVOKE UPDATE ON game TO 'User2'@'localhost'; User2 - > UPDATE game SET price= 1500 WHERE gameid= 9141 ;
Data security is very important in Database creation. As database is connected to various applications, the data of the user must be safe. The data authorization is required so only those users who have proper authorization can access the data, because some databases are so complex that any small change can destroy or crash the data. These grant and revoke are used so that only some part of database can be viewed by others as stated by the authorizer of database. Thus, we have studies importance of authorization in Database.
