Relational Constraints and Schema in SQL Databases, Assignments of Database Management Systems (DBMS)

Download Relational Constraints and Schema in SQL Databases and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!

ASSIGNEMENT

FOR WINTER

VACATION

(1) Write difference between Constraints and Schema.

Give the name of five applications that are used in

database and why? Give reason

Answer- SQL Constraints SQL constraints are used to specify rules for the data in a table. Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted. Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table. The following constraints are commonly used in SQL:  NOT NULL - Ensures that a column cannot have a NULL value  UNIQUE - Ensures that all values in a column are different  PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table  FOREIGN KEY - Uniquely identifies a row/record in another table  CHECK - Ensures that all values in a column satisfies a specific condition  DEFAULT - Sets a default value for a column when no value is specified  INDEX - Used to create and retrieve data from the database very quickly

 Telecom : There is a database to keeps track of the information regarding calls made, network usage, customer details etc. Without the database systems it is hard to maintain that huge amount of data that keeps updating every millisecond.  Industry : Where it is a manufacturing unit, warehouse or distribution Centre, each one needs a database to keep the records of ins and outs. For example distribution Centre should keep a track of the product units that supplied into the Centre as well as the products that got delivered out from the distribution Centre on each day; this is where DBMS comes into picture.  Banking System : For storing customer info, tracking day to day credit and debit transactions, generating bank statements etc. All this work has been done with the help of Database management systems.  Sales : To store customer information, production information and invoice details.  Airlines : To travel though airlines, we make early reservations, this reservation information along with flight schedule is stored in database.  Education sector : Database systems are frequently used in schools and colleges to store and retrieve the data regarding student details, staff details, course details, exam details, payroll data, attendance details, fees details etc. There is a hell lot amount of inter-related data that needs to be stored and retrieved in an efficient manner.  Online shopping : You must be aware of the online shopping websites such as Amazon etc. These sites store the product information, your addresses and preferences, credit details and provide you the relevant list of products based on your query. All this involves a Database management system

(2) What is the use of relational constraints and relational Schema in the relational data model? Explain with example Answer-

Relational Constraints and Relational Schema

So far, we have discussed the characteristics of single relations. In a relational data-base, there will typically be many relations, and the tuples in those relations are usually related in various ways. The state of the whole database will correspond to the states of all its relations at a particular point in time. There are generally many restrictions or constraints on the actual values in a database state. These constraints are derived from the rules in the mini world that the database represents Constraints that are inherent in the data model. We call these inherent model-based constraints or implicit constraints. Constraints that can be directly expressed in schemas of the data model, typically by specifying them in the DDL (data definition language). We call these schema-based constraints or explicit constraints. Constraints that cannot be directly expressed in the schemas of the data model, and hence must be expressed and enforced by the application pro-grams. We call these application- based or semantic constraints or business rules.

1 Domain Constraints

Domain constraints specify that within each tuple, the value of each attribute A must be an atomic value from the Domain. We have already discussed the ways in which domains can be specified in Section. The data

Relational Databases and Relational Database

Schemas

The definitions and constraints we have discussed so far apply to single relations and their attributes. A relational database usually contains many relations, with tuples in relations that are related in various ways. In this section we define a relational database and a relational database schema. A relational database schema S is a set of relation schemas S = { R 1 , R 2 , R 3 } and a set of integrity constraints IC. A relational database state DB of S is a set of relation states DB = { r 1 , r 2 , r 3 } such that each r is a state of R and such that the r relation states satisfy the integrity constraints specified in IC. Shows a relational database schema that we call COMPANY = {EMPLOYEE, DEPARTMENT, DEPT_LOCATIONS, PROJECT, WORKS_ON, DEPENDENT}. The underlined attributes represent primary keys. Shows a relational database state corresponding to the COMPANY schema. We will use this schema and database state in this chapter and in Chapters 4 through 6 for developing sample queries in different relational languages. (The data shown here is expanded and available for loading as a populated database from the Companion Website for the book,

1. Insert Query We use Insert Command to insert records into a table. The SQL INSERT INTO declaration is used to include new database rows in the database table. Syntax: INSERT INTO TABLE_NAME (column1, column2, column3)] VALUES (value1, value2, value3); Here, column1, column2 column3 are the names of the columns in the table into which you want to insert data. If you add value for all columns of the table you may not have to specify the column(s) name in the SQL query. Nonetheless, notice that the order of the values is identical to that of the table columns. It will be the following SQL INSERT INTO syntax: Syntax: INSERT INTO TABLE_NAME (Column1, Column2, Column3) VALUES (value1, value2, value3); Example:

As per the syntax of INSERT INTO syntax, we specify the name of the table "INSERT INTO" keyword, followed the order of columns, in which you want the values that need to be inserted. Post the "VALUES' keyword enter the column values in order of the columns specified earlier.

1. INSERT INTO student (id, name , age) VALUES (‘1’, ‘Amit’, ‘Noida’, 28); After firing this query, our table will look like: ID Name Address Age 1 Amit Noida 26 We can fire more such queries to fill records in our table:
2. Insert into student (id, name , address, age) valu es (‘2’, 'Amit', ‘New Delhi ‘23’);
3. Insert into student (id, name , address, age) valu es (‘3’, 'Rohan', ‘Bareilly’ ‘27’); So the table now becomes: ID Name Address Age 1 Amit Noida 26 2 Amit New Delhi 23 3 Rohan Bareilly 27

2 Amit New Delhi 23 3 Rohan Bareilly 27 We may also use ORDER BY to organize the presented outcome in a similar order in our chosen sentence. For example,

1. SELECT * FROM student ORDER BY age; Result is: ID Name Address Age 1 Amit New Delhi 23 2 Amit Noida 26 3 Rohan Bareilly 27 The production is arranged in an increasing age sequence. When we choose to structure the view in decreasing order we should use the DESC keyword after the column name in the query.
2. Viewing only selected records from a table When we do not want all records to fill our display screen, SQL offers the choice to show only selected rows while there are a number of rows in a database. Count is good for recording record numbers.

Syntax: SELECT COUNT (1) FROM TABLE_NAME; Example:

1. SELECT COUNT(1) FROM student; The output of this query will be: ID Name Address Age 1 Amit Noida 26 If we fire: The number of rows our table has shall be returned. In our query, we may even use MAX & MIN. For Example, A student with a maximum age needs to be provided with information, we can fire:
2. SELECT id , name , MAX (age) FROM student; We will get: ID Name Address Age 3 Rohan Bareilly 27 We can also check sum of a numeric column.

1 Amit Noida 26 2 Amit New Delhi 23

5. Changing data in existing records in a table Syntax: The basic syntax of UPDATE query with WHERE clause is as follows: UPDATE TABLE_NAME SET column1 = value1, column2 = value2, column3 = value NWHERE [condition]; Assume that we want to alter a student's age in our table called 'Rohan.' The following query will be used: Example: 1. UPDATE student SET age = 28 WHERE name = ‘Rohan’; You may have found that while the values are characters, we define the term in single quotes. That's an obligation. Now if we fire:
   1. SELECT * FROM student; We will get the following table as output:

ID Name Address Age 1 Amit Noida 26 2 Amit New Delhi 23 3 Rohan Bareilly 27 Please pay attention to UPDATE or DELETE queries using the WHERE clause. Suppose there is more than one student called 'Rohan' in our 'class' list. The age of all students called 'Rohan' is modified to 28 in this case. Therefore, the PRIMARY KEY should always be included in the WHERE clause during the modification or deletion. We must always take note of the column data types when we modify the details. A numeric column can only contain numbers while a text column can contain text. This implies that if we use an UPDATE clause to place age = 'Rohan' in the age list, SQL would make an exception. You can find out more about SQL's error types and exceptions.

 Department ID  ) Here in Employee table Employee ID & SSN are eligible for a Primary Key and thus are Candidate keys. (5) What do you mean by Selection and Projection operator? Discuss their use in SQL based queries. Answer- 1 Selection : This operation chooses the subset of tuples from the relation that satisfies the given condition mentioned in the syntax of selection. Notation – σ (^) c (R) Here, ‘c’ is selection condition and ‘σ (sigma)’ is used to denote Select Operator.

2. Projection : This operation selects certain required attributes, while discarding other attributes. Notation – Π (^) A (R) Where ‘A’ is the attribute list, it is the desired set of attributes from the attributes of relation(R) symbol ‘π(pi)’ is used to denote the Project operator,

R is generally a relational algebra expression, which results in a relation. Example