Dept. of Computer Science Engineering, School of Engineering, Anurag Group of Institutions
II B.Tech II Semester
Academic Dairy
for
Database Management Systems
Faculty:Mr.J.Shiva Prashanth
Mr. Pavan Kumar
Syllabus:
Database System Applications, data base System vs File system – View of Data – Data Abstraction – Instances and Schemas – data models –the ER Model – Relational Model –other Models – Database Languages – DDL – DML – database access applications programs – data base users and administrator – transaction management – data base system structure – storage manager – the Query Processor
Objective of the Unit:
· Understanding data, information, database and DBMS.
· Development and need of DBMS
· Understanding architecture of DBMS, People associated with the Database
· Necessity and benefits of E-R diagram
Plan of Lecture Delivery:
Topic / No of Hours / Date / RemarkInformation, Data, Database, DBMS, File System / 01
Views, Instances, Abstraction and Schema / 01
Data Models, ER diagrams, ER Model / 01
Relational Model, Other Models, Database Languages – DDL, DML / 01
Tutorial / 01
People associated with the Database – users, administrators and developers / 01
Database, DBMS, Application programs, Transaction Management / 01
DBMS structure, Storage Manager / 01
Query Processor, Revision of Topics / 01
Total / 09
Important Questions
One mark questions and answers:-
1. what are the main functions of DBA
· authorizing users to access the database
· coordinating/monitoring its use
· acquiring hardware/software resources for upgrades
2. what is meant by data independence
Data independence is the capacity to change the schema at one level of the architecture without having to change the schema at the next higher level. We distinguish between logical and physical data independence according to which two adjacent levels are involved. The former refers to the ability to change the conceptual schema without changing the external schema. The latter refers to the ability to change the internal schema without having to change the conceptual.
Logical Data Independence:
The capacity to change the conceptual schema without having to change the external schemas and their associated application programs.
Physical Data Independence:
The capacity to change the internal schema without having to change the conceptual schema.
3. what are advantages of views:
Views are virtual (not real but in effect) tables or relations which are based on user’s view of particular data base.
4. what is relational schema
· Representation of relational database's entities, attributes within those entities, and relationships between those entities
· Represented as DDL or Visually
· Example: Employee
Ename, Eid,sal, bdate, hiredate, sex where primary key is underlined
5. what is DDL
· DDL means Data Definition Language
· Used by the DBA and database designers to specify the conceptual schema of a database.
· In many DBMSs, the DDL is also used to define internal and external schemas (views).
· DDL commands are
CREATE
ALTER
TRUNCATE
6. what is Cartesian product
· This operation is used to combine tuples from two relations in a combinatorial fashion.
· Denoted by R(A1, A2, . . ., An) x S(B1, B2, . . ., Bm)
· Result is a relation Q with degree n + m attributes:
i. Q(A1, A2, . . ., An, B1, B2, . . ., Bm), in that order.
· The resulting relation state has one tuple for each combination of tuples—one from R and one from S.
· Hence, if R has nR tuples (denoted as |R| = nR ), and S has nS tuples, then R x S will have nR * nS tuples.
· The two operands do NOT have to be “type compatible”
7. what is data model
A data model ---a collection of concepts that can be used to describe the conceptual/logical structure of a database--- provides the necessary means to achieve this abstraction.
By structure is meant the data types, relationships, and constraints that should hold for the data. Most data models also include a set of basic operations for specifying retrievals/updates.
8. what is data redundancy
Repeating the same data again and again is nothing but redundancy.Data redundancy (such as tends to occur in the "file processing" approach) leads to wasted storage space, duplication of effort (when multiple copies of a datum need to be updated), and a higher likelihood of the introduction of inconsistency.
9. write about Naïve users
· Naive/Parametric end users: Typically the biggest group of users; frequently query/update the database using standard canned transactions that have been carefully programmed and tested in advance. Examples:
ii. bank tellers check account balances, post withdrawals/deposits
iii. reservation clerks for airlines, hotels, etc., check availability of seats/rooms and make reservations.
iv. shipping clerks (e.g., at UPS) who use buttons, bar code scanners, etc., to update status of in-transit packages.
10. what is DBMS
Database management system is software of collection of small programs to perform certain operation on data and manage the data.
Two basic operations performed by the DBMS are:
· Management of Data in the Database
· Management of Users associated with the database.
11. what is Relational algebra
· Relational algebra and relational calculus are formal languages associated with the relational model.
· Informally, relational algebra is a (high-level) procedural language and relational calculus a non-procedural language.
· Relational algebra operations work on one or more relations to define another relation without changing the original relations.
12. define catalog
system catalog, which contains a description of the structure of each file, the type and storage format of each field, and the various constraints on the data (i.e., conditions that the data must satisfy).
The system catalog is used not only by users (e.g., who need to know the names of tables and attributes, and sometimes data type information and other things), but also by the DBMS software, which certainly needs to "know" how the data is structured/organized in order to interpret it in a manner consistent with that structure.
13. define Data Dictionary
Data dictionary / repository:
Used to store schema descriptions and other information such as design decisions, application program descriptions, user information, usage standards, contains all information stored in catalog, but accessed by users rather than dbms.
· describes the (logical) structure of the whole database for a community of users. Hides physical storage details, concentrating upon describing entities, data types, relationships, user operations, and constraints. Can be described using either high-level or implementational data model.
Applications:
1) Idea of Format of Data
2) Logical storage of Data
3) Structure of DBMS
Students Expected to Learn:
1) Structure of Data, Database, Database Management Systems
2) Views and Levels of Abstraction
3) Database Languages – DDL, DML
Unit-II
Syllabus:
History of Database Systems, Database Design and ER diagrams, Beyond ER design entities, Attributes and Entity Sets, Relationships and Relationship Sets, Additional features of ER model, Concept Design with ER Model, Conceptual Design for Large Enterprises.
Objectives:
1) To learn about the ER diagrams
2) Concepts of Attributes and Entity sets
3) Relationship and Relationship sets
4) Learn about the Design issues of a database
Time Table:
Topic / No of Hours / DateHistory of DBMS, Database design, ER design, / 01
Beyond ER design, Entities, Attributes / 01
Entities, Relationship and Relationship sets / 01
Additional Features of ER model / 01
Tutorial / 01
Concept design with ER model / 01
Conceptual design for Large Database Systems / 01
Total / 07
Important Questions:
Previous one mark questions up to first two units:-
14. what are the main functions of DBA
15. what is meant by data independence
16. what are advantages of views
17. what is relational model
18. what is referential integrity
19. what is meant by multivalued attribute
20. what is relational schema
21. what is DDL
22. what is Cartesian product
23. what is integrity constraint
24. what is meta data
25. note on update command
26. what is data model
27. list all types of SQL
28. what is data redundancy
29. write about Naïve users
30. what is an weak entity set
31. write about composite attribute
32. define candidate key
33. what is DBMS
34. what is RDBMS
35. what is Relational algebra
36. write the use of multivalued attribute
37. what are sql languages
38. define catalog
39. define Data Dictionary
40. define conceptual schema
41. what is relation ship instance
42. what is key constraint
43. what is foreign key constraint
44. how to represent multivalued attribute in E.R model
previous paper long answer questions
1. Describe the three schema architecture. Why do we need mapping b/w schema levels
2. list the cases in which null values are appropriate with examples
3. differentiate b/w FPS and DBMS
4. design a conceptual data base design for health insurance system
5. compare and contrast Relational model and Hierarchical model
6. explain the basic operations of Relational Algebra with examples
7. Draw and explain the DBMS component modules
8. what are advantages of DBMS
9. explain the difference b/w among entity, entity type and relation ship set
10. what is integrity constraint explain deferent constraints in DBMS
11. what are the functions of DBA
12. write about architecture of DBMS
13. explain about various database users
14. what are various capabilities of DBMS
15. what is the difference b/w logical data independence and physical data independence
16. discuss the the main types of constraints on specialization and generalization
17. what is e-r model .explain the components E-R model
18. what is sql and various types of commands
19. explain about relation model and advantages of rm.
APPLICATIONS:
1) Writing ER Model
2) Design issues of Database
Unit-III
Syllabus:
Introduction to Relational Model – Integrity constraint over relations, enforcing integrity constraints, querying relational data, logical database design, introduction to views, destroying/altering tables and views.
Relational Algebra – Selection and Projection set operations, renaming, joins, divisions, Example of Algebra overviews, relational calculus, tuple relational calculus, domain relational calculus, expressive power of algebra and calculus.
Objectives of Unit:
1) Introduction to Relational Model, Integrity constraints over relations
2) Querying relational data, logical database design, introduction to views and tables
3) Relational algebra – projection and selection, relational calculus
4) Domain relational calculus, expressive power of algebra and calculus
Teaching Plan:
Topic / No of Hours / Date / RemarkRelational Model, Integrity Constraints, Querying relational model / 01
Logical Database design, introduction to views / 01
Destroying/altering tables / 01
Relational Algebra / 01
Tutorial / 01
Selection and Projection / 01
Joins, renaming / 02
Tuple relational Calculus / 01
Domain relational calculus / 01
Tutorial / 01
Expressive power of algebra and calculus / 01
Total / 12
Important Questions:
Requirements Analysis
– user needs; what must database do?
Conceptual Design
– high level description (often done with ER model)
Logical Design
– translate ER into DBMS data model(Relational model)
(NOW)Schema Refinement
– consistency,normalization
Physical Design
- indexes, disk layout
Security Design
- who accesses what
Good Database Design
• no redundancy of FACT (!)
• no inconsistency
• no insertion, deletion or update anomalies
• no information loss
• no dependency loss
Informal Design Guidelines for Relational Databases
1. Semantics of the Relation Attributes
2. Redundant Information in Tuples and Update Anomalies
3. Null Values in Tuples
4. Spurious Tuples
1. Semantics of the Relation Attributes
GUIDELINE 1: Informally, each tuple in a relation should represent one entity or relationship instance. (Applies to individual relations and their attributes).
o Attributes of different entities (EMPLOYEEs, DEPARTMENTs, PROJECTs) should not be mixed in the same relation
o Only foreign keys should be used to refer to other entities
o Entity and relationship attributes should be kept apart as much as possible.
Design a schema that can be explained easily relation by relation. The semantics of attributes should be easy to interpret.
2. Redundant Information in Tuples and Update Anomalies
n Information is stored redundantly
o Wastes storage
o Causes problems with update anomalies
§ Insertion anomalies
§ Deletion anomalies
§ Modification anomalies
Consider the relation:
EMP_PROJ(Emp#, Proj#, Ename, Pname, No_hours)
Insertion anomalies
Cannot insert a project unless an employee is assigned to it.
Deletion anomalies
a. When a project is deleted, it will result in deleting all the employees who work on that project.
b. Alternately, if an employee is the sole employee on a project, deleting that employee would result in deleting the corresponding project.
Modification anomalies
Changing the name of project number P1 from “Billing” to “Customer-Accounting” may cause this update to be made for all 100 employees working on project P1.
GUIDELINE 2:
n Design a schema that does not suffer from the insertion, deletion and update anomalies.
n If there are any anomalies present, then note them so that applications can be made to take them into account.
3. Null Values in Tuples
GUIDELINE 3:
n Relations should be designed such that their tuples will have as few NULL values as possible
n Attributes that are NULL frequently could be placed in separate relations (with the primary key)
n Reasons for nulls:
n Attribute not applicable or invalid
n Attribute value unknown (may exist)
n Value known to exist, but unavailable
Applications:
1) Understanding the nature of DBMS
2) Relational Algebra and calculus
Students expected to learn:
Relational Algebra, relational calculus
Unit-IV
Syllabus:
Form of Basic SQL query, Examples of basic SQL queries, introduction to nested queries, correlated nested queries set, comparison operators, aggregate operators, NULL values, comparison using NULL values, Logical Connectivity’s, Impact on SQL constructs, Outer joins, disallowing NULL values, complex integrity constraints in SQL triggers and active databases.