Data Definition Language (DDL)

What is DDL? / Data Definition Language / Role of Data Definition Language / What can a DDL statement do? / Important aspects of DDL statements

Data Definition Language (DDL)

As specified in the name, DDL is used to define database schemas. With DDL statements we can do the following;

• To create databases.

For example we can create a database named University in MySQL as follows;

CREATE DATABASE University;

• To create table structures.

For example, we can create a table named Student in Oracle as follows;

CREATE TABLE student (Regno NUMBER(10), Name VARCHAR(30));

• To change the structure of the tables.

We can alter the structure of student table by adding/dropping columns, by changing the size of the accepted values etc. For example, the following statement adds a new column with Student table in Oracle;

ALTER TABLE student ADD COLUMN Phone Number(10);

• To remove tables.

We can delete the whole table structure Student using the following statement in Oracle;

DROP TABLE student;

• To rename tables.

To rename an existing table, we can use the following query in Oracle;

RENAME student TO student_table;

• To define referential integrities.

We can define referential integrities like primary key constraints, check constraints, specific type etc using DDL statements. For example, the following query creates a table Teacher with primary key constraint.

CREATE TABLE Teacher(Id NUMBER(3) PRIMARY KEY, Name VARCHAR(30));

• To analyze information.

We can analyze a table, index or clusters for handling performance related issues.

• To add comments to the Data Dictionary.

We can add comments to tables, table columns when they are stored in Data Dictionary. For example, the statement will add a comment ‘Name of the teacher’ with the column definition Name of Teacher table.

COMMENT ON COLUMN Teacher.Name IS ‘Name of the teacher’;

When we execute DDL statements, it does the following things;

1. It does the required things as specified through query. [For example, as mentioned above]


2. Update the special table called Data Dictionary. The Data Dictionary gets updated every time you execute one of the DDL statements.

Decomposition into BCNF - Examples

Normalization Examples / Decomposition into BCNF tables / How to decompose a table into BCNF compliant table? / BCNF decomposition examples

Here, I have listed some examples and links for decomposing a relation into a perfect BCNF relation.

1. Simple and neatly explained example of BCNF decomposition - Click here.

2. Method for normalizing a relation to BCNF based on functional dependencies. Steps are explained. Click here.

cs2255-database-management-systems-november-december-2014-anna-university-question-paper-with-answers

CS2255 Database Management Systems question paper - Nov/Dec 2014 / Anna University Previous Year 2014 Exam Questions / Anna University Previous Year 2014 Computer Science and Information Technology Question Papers / CS 2255/CS 46/CS 1254/080250009/10144 CS 406 — Database Management Systems Question with Answers

Click on the links for answers;

Question Paper Code : 91347

B.E./B.Tech. DEGREE EXAMINATION, NOVEMBER/DECEMBER 2014.

Fourth Semester

Computer Science and Engineering

CS 2255/CS 46/CS 1254/080250009/10144 CS 406 — DATABASE MANAGEMENT SYSTEMS

(Common to Information Technology)

(Regulation 2008/2010)

(Common to PTCS 2255/10144 CS 406 – Database Management Systems for

B.E. (Part-Time) Third Semester – Computer Science and Engineering

Regulation 2009/2010)

Time : Three hours                                                             Maximum : 100 marks

Answer ALL questions.

PART A — (10 × 2 = 20 marks)

1. What is physical, logical and view level data abstraction?

2. What do you mean by simple and composite attribute?

3. Explain the use of assignment operator in relational algebra with an example.

4. What is the use of UNIQUE statement?

5. Define trivial functional dependency.

6. What is meant by referential integrity?

7. What is a cascading update?

8. What are the disadvantages of not controlling concurrency?

9. What is a heap file? How pages are organized in a heap file?

10. What is a catalog?

PART B — (5 × 16 = 80 marks)

11. (a) (i) Why would you choose a  database system instead of simply storing data in operating system files? When would it make sense not to use a database system?

(ii) Explain the difference between logical and physical data independence. (8 + 8)

Or

(b) Notown Records has decided to store information about musicians who perform in its albums (as well as other company data) in a database. The company has wisely chosen to hire you as a database designer.

Each musician that records at Notown has an SSN, a name, an address, and a phone number. Poorly paid musicians often share the same address, and no address has more than one phone.

Each instrument used in songs recorded at Notown has a unique identification number, a name (e.g., guitar, synthesizer, flute) and a musical key (e.g., C, B-flat, E-flat).

Each album that is recorded on the Notown label has a title, a copyright date, a format (e.g., CD or MC), and an album identifier.

Each song recorded at Notown has a title and an author.

Each musician may play several instruments, and a given instrument may be played by several musicians.

Each album has a number of songs on it, but no song may appear on more than one album.

Each song is performed by one or more musicians, and a musician may perform a number of songs.

Each album has exactly one musician who acts as its producer.  A musician may produce several albums, of course.

Design a conceptual schema for Notown and draw an ER schema for your schema. The preceding information describes the situation that the Notown database must model. Be sure to indicate all key and cardinality constraints and any assumptions that you make. Identify any constraints that you are unable to capture in the ER diagram and briefly explain why you could not express them.

12. (a) Consider the following schema;

Supplier(sid: integer, sname: string, address: string)

Parts(pid: integer, pname: string, color: string)

Catalog (sid: integer, pid: integer, cost: real)

The key fields are underlined, and the domain of each field is listed after the field name. Therefore sid is the key for suppliers, pid is the key for parts, and sid and pid together form the key for catalog. The catalog relation has the prices charged for parts by suppliers. Write the following queries in relational algebra.

(i) Find the sids of suppliers who supply a red part or green part.

(ii) Find the sids of suppliers who supply every part.

(iii) Find the sids of suppliers who supply every red part or supply every green part.

Or

(b) Briefly explain about fundamental, and additional operations in SQL with example.

13. (a) (i) What are the threedata anomalies that are likely to occur as a result of data redundancy? Can data redundancy be completely eliminated in database approach? Why or why not? (6)

(ii) Explain the process ofnormalization from 1NF to BCNF stage with example. (10)

Or

(b) (i) Define the domain relational calculus. (6)

(ii) Given R(A,B,C,D,E) with the set of FDs, F(ABàCD, ABCàE, CàA).

(1) Find any two candidate keys of R.

(2) What is the normal form of R? Justify. (10)

14. (a) (i) Define and differentiate between Deadlock prevention, Deadlock detection, and Deadlock avoidance. (6)

(ii) Explain different locking mechanism used in lock based concurrency control. (10)

Or

(b) (i) What are the deferred modification and immediate modification technique for recovery? How does recovery takes place in case of a failure in these techniques? (8)

(ii) Explain timestamp based concurrency control with and without Thomas write rule. Give example. (8)

15. (a) (i) Explain the differences between Hash indexes and B+ tree indexes. In particular, discuss how equality and range searches work, using an example. (6)

(ii) Explain the structure of B+ tree. How to process queries in B+ tree? (10)

Or

(b) What is RAID? Briefly explain different levels of RAID. Discuss the factors to be considered in choosing a RAID level. (16)

***********

CS2029 Advanced Database Technology Anna University question paper - November/December 2014

CS2029 Advanced Database Technology question paper - November/December 2014 / Anna University Previous Year 2014 Exam Questions for Advanced Database Technology with Answers / Anna University Previous Year 2014 Computer Science and Information Technology Question Papers / CS 606 Advanced Database Technology Question Paper / CS2029 Sixth Semester Advanced Database Technology question / CS2029 CS606 Question paper with answers

Question Paper Code : 91325

B.E./B.Tech. DEGREE EXAMINATION, NOVEMBER/DECEMBER 2014

Sixth Semester

Computer Science and Engineering

CS 2029/CS 606/10144CSE 23 — ADVANCED DATABASE TECHNOLOGY

(Common to Seventh Semester Information Technology)

(Regulation 2008/2010)

(Common to PTCS 2029/10144 CSE 23 – Advanced Database Technology for B.E.

(Part-Time) Fifth/Sixth Semester – CSE – Regulation 2009/2010)

Time : Three Hours                                                          Maximum : 100 marks

Answer ALL Questions

PART A – (10 X 2 = 20 marks)

1. Define query processing.

2. Mention the use of database tuning.

3. State any two features of distributed database.

4. Define concurrency control.

5. What is the use of JASMINE model?

6. Write any two features of POSTGRES model.

7. Define data mining.

8. Mention main difference between mobile and web database.

9. What is multimedia database?

10. Give any features of spatial database.

PART B – (5 X 16 = 80 marks)

11. (a) (i) Explain about third normal form and BCNF with an example. (8)

(ii) Discuss query optimization briefly. (8)

Or

(b) (i) Write short note on transaction processing. (8)

(ii) Discuss two phase locking protocol technique for concurrency in detail. (8)

12. (a) Discuss aboutinterquery and intraquery parallelism with example. (16)

Or

(b) (i) Explain the architecture of Distributed database with a neat diagram. (10)

(ii) Write about commit protocols briefly. (6)

13. (a) Discuss the modeling and design of object oriented database in detail. (16)

Or

(b) Explain the overview of GEMSTONE and ODMG models. (16)

14. (a) (i) Distinguish between OLTP and OLAP. (8)

(ii) What are the different schemas in data warehouse? Explain them. (8)

Or

(b) Write about mobile, XML, and web databases with their structures. (16)

15. (a) (i) Explain about knowledge bases, active and deductive databases. (16)

Or

(b) Discuss the concepts of multimedia data structures and query languages briefly. (16)

_________________________