Unit-1

Database system architecture

Q1) Define data dependency with their types ?

A1) Data dependency

In addition to users' data, a database system usually includes a lot of data. For instance, to easily locate and retrieve data, it stores information about data, known as metadata. Modifying or modifying a collection of metadata once it is placed in the database is very difficult. But when a DBMS expands, in order to fulfil the users' requirements, it needs to evolve over time. It will become a boring and highly complicated job if the entire data is based.

Metadata itself follows a layered model, such that it does not impact the data at another level when we modify data on one layer. This data is independent, but is mapped to one another.

Types of data independences

• Physical data independence

Physical Data Independence is defined as the ability to make adjustments without affecting the higher-level schemas in the structure of the lowest level of the Database Management System (DBMS). Therefore, the Physical level adjustment does not result in any adjustments to the Conceptual or View levels.

• Logical data independence

Logical Data Independence is defined as the ability to make improvements to the Database Management System (DBMS) middle level structure without affecting the schema or application programmes at the highest level. Changes to the conceptual level should also not result in any changes to the view levels or programmes of the application.

Q2) What is the Entity-Relationship Model?

A2) Entity-Relationship Model

●      The logical structure of a database can be expressed by an E-R diagram.

●      Relationships are defined in this database model by dividing the object of interest into an entity and its characteristics into attributes.

●      Using relationships, various entities are related.

●      To make it easier for various stakeholders to understand, E-R models are established to represent relationships in pictorial form.

The Main Components of ER Diagram are: -

a) Rectangles - Entity Sets

b) Ellipses - Attributes

c) Diamond - Relation among entity sets

d) Lines - Connects attributes to entity sets and entity sets to relationships.

The above E-R diagram has two entities: Teacher and Department

Teacher entity has three attributes:

Teacher_id

Teacher_name

Teacher_Subject

Department entity has two attributes: -

Dept_id

Dept_name

Relation Teaches in gives relationship between Teacher, Department.

Relationship can be of the type:

1:1→One to one

1:M →One to many

M:1 →Many to one

M:M →Many to many

Q3) What is the data definition language ?

A3) Data definition language

In reality, the DDL or Data Description Language consists of SQL commands that can be used to define the schema for the database. It basically deals with database schema definitions and is used to construct and change the configuration of database objects in the database.

Some of the commands that fall under DDL are as follows:

CREATE

It is used in the database for the development of a new table

Syntax

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Example

CREATE TABLE STUDENT(Name VARCHAR2(20), Address VARCHAR2(100), DOB DATE);

ALTER

It is used to modify the database structure. This move may be either to change the characteristics of an existing attribute or to add a new attribute, likely.

Syntax

In order to add a new column to the table:

ALTER TABLE table_name ADD column_name COLUMN-definition;   

To change the current table column:

ALTER TABLE MODIFY(COLUMN DEFINITION....); 

Example

ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20)); 

ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20)); 

Drop

It is used to delete both the structure of the table and the record.

Syntax

DROP TABLE ; 

Example

DROP TABLE STUDENT; 

TRUNCATE

It is used to erase from the table all rows and free up the space containing the table.

Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE TABLE STUDENT;

Q4) Write short notes on network models ?

A4) Network model

●      Network model uses two different data structures: -

a) A record type is used to represent as entity set.

b) A set type is used to represent a directed relationship between two record types.

●      This is the Hierarchical model's extension. Data is arranged more like a graph in this model, and is allowed to have more than one parent node.

●      Data is more linked in this database model as more relationships are formed in this database model. Also, as the data is more linked, it is also simpler and quicker to access the information. To map many-to-many data relationships, this database model was used.

●      This was the most commonly used database architecture prior to the advent of the Relational Model.

Q5) Describe the term domain constraints and entity integrity constraints ?

A5) Domain constraints

●      As a description of a valid set of values for an attribute, domain constraints can be specified.

●      The domain data type consists of a string, character, integer, time, date, currency, etc. In the corresponding domain, the value of the attribute must be available.

Entity Integrity Constraints

●      The honesty restriction of the organization states that the primary key value should not be zero.

●      This is because the primary key value is used to define the relationship between individual rows, and if the primary key has a null value, then those rows cannot be identified.

●      A table may contain a null value other than the field for the primary key.

Q6) Define relational model?

A6) Relational model

●      Relational model uses a collection of tables to represent both data and the relationships among those data.

●      This model is useful for constructing a database that can then be converted into relational model tables.

●      Data is arranged in two-dimensional tables in this model and the relationship is preserved by storing a common field.

●      In the relational model, the basic structure of data is tables. In the rows of that table, all information relating to a specific category is stored.

Therefore, in the relational model, tables are also known as relations.

Sample relational database

1. Teacher Table

B.     Department Table

C.    Teaches in table

Q7) Write short notes on data manipulation language?

A7) Data manipulation language

To change the database, DML commands are used. It is responsible for all forms of database changes.

The DML command is not auto-committed, which means all the changes in the database will not be permanently saved. There may be rollbacks.

Commands under DML

a. INSERT

The INSERT statement is a query from SQL. It is used for the insertion of data into a table row.

Syntax

INSERT INTO TABLE_NAME   

(col1, col2, col3,.... Col N) 

VALUES (value1, value2, value3, .... ValueN);

Or

INSERT INTO TABLE_NAME   

VALUES (value1, value2, value3, .... ValueN);  

b. UPDATE

This command is used to update or alter the value of a table column.

Syntax

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

c. DELETE

It is used to extract a table from one or more rows.

Syntax

DELETE FROM table_name [WHERE condition]; 

Types of DML: -

a. Procedural: -

It requires a user to specify what data is needed and how to retrieve it from database. The user instructs the system to perform a sequence of options on the database to compute the desired result.

b. Non-Procedural: -

It requires a user to specify what data is needed without specifying how to get it.

Q8) Define the term Referential Constraints and key Constraints?

A8) Referential constraints

●      Between two relations or tables, the referential integrity constraints are defined and used to preserve the consistency between the tuples in two relationships.

●      If an attribute of the foreign key of the relationship R1 has the same domain(s) as the primary key of the relationship R2, then the foreign key of R1 is said to refer to or refer to the primary key of the relationship R2.

●      Foreign key values in the R1 relationship tuple can either take the primary key values for a certain R2 relationship tuple, or they can take NULL values, but cannot be zero.

Key Constraints

●      Keys are the collection of entities used to uniquely define an object within its entity set.

●      A group of entities may have several keys, but the primary key will be one key. A primary key may contain in the relational table a unique and null value.

Q9) What do you mean by data abstraction?

A9) Data abstraction

●      A major objective of database systems is to provide users with an abstract view of the data.

●      The system hides details of how the data is stored and maintained.

●      Database systems has to provide an efficient mechanism for data retrieval.

●      Efficiency leads to the design of complex data structure for representation of data in databases.

●      This complexity must be hidden from users.

●      This can be done by providing several levels of abstractions.

●      In the Three Schema Architecture, three levels of abstraction have been created. 

These are: -

➢      Logical level : This is the middle stage of the architecture of 3-level data abstraction. This explains what information is contained in the database.

➢      View level : Highest data abstraction standard. This level defines the relationship between users and the database system.

➢      Physical level : This is the lowest data abstraction level. This explains how the data in the database is actually stored. At this step, you can get the complex information of the data structure.

Q10) Describe Object oriented data model?

A10) Object oriented data model

The object-oriented database derivation is the integrity of object-oriented

Programming language systems and consistent systems. The power of the object-oriented databases comes from the cyclical treatment of both consistent data, as found in databases, and transient data, as found in executing programs.

Object-oriented databases use small, recyclable separated of software called objects. The objects themselves are stored in the object-oriented database. Each object contains of two elements:

1. Piece of data (e.g., sound, video, text, or graphics).

2. Instructions, or software programs called methods, for what to do with

The data.

There are two types of ORM: -

a. Object-Oriented Data Model (OODM)

b. Object-Relational Data Model (ORDM)

These models are used to handle objects:

1) To provide support for complex objects.

2) To provide user extensibility for data types operators and access methods.

3) There should be mechanism to support:

a) Abstract data type.

b) Data of type ‘procedure’.

c) Rules.