Q9.)
Advantages of OODBMS
- Enriched modeling capabilities
- Extensibility
- Removal of impedance mismatch
- More expressive query language
- Support for schema evolution
- Support for long-duration transaction
- Applicability to advanced database applications.
- Improved performance
Disadvantages of OODBMS
- List of universal data model
- Lack of experience
- Lack of standards
- Competition
- Query optimization compromises encapsulation
- Locking at Object level may impact performance
- Complexity
- Lack of support for views
- Lack of support for security
1.) JDBC is a Java-based tool used to connect SQL databases.It was created at Sun Microsystems as a base for building alternate tools and interfaces.
2.) JDBC can be used to connect directly to a Java-aware DBMS. And embedded SQL for java with this approach, JDBC requires that SQL statements be passed as strings to Java methods. An embedded SQL preprocessor allows a programmer instead to mix SQL statements directly with java. For example java variables can be used in an SQL statement to receive or provide SQL values.
3.) Direct mapping of relational database tables to Java classes in this object relation mapping, each row of the table becomes an instance of that class, and each column value corresponds to an attribute of that instance.
4.) JDBC is modeled after the ODBC, originally designed and used by Microsoft. ODBC is based on the C language.
5.) JDBC API is a natural Java Interface and is built on ODBC. JDBC retains some.
- JDBC drivers are written in java and JDBC code is automatically installable, secure, and portable on all platforms.
Atomicity
Main article: Atomicity (database systems)
Atomicity requires that each transaction is "all or nothing": if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.
Consistency
Main article: Consistency (database systems)
The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraints, cascades, triggers, and any combination thereof.
Isolation
Main article: Isolation (database systems)
The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e. one after the other. Each transaction must execute in total isolation i.e. if T1 and T2 execute concurrently then each should remain independent of the other.[citation needed]
Durability
Main article: Durability (database systems)
Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter).
- LOST UPDATE Problem
- Uncommitted Dependency Problem
- Inconsistent analysis problem
Schedule : is sequence of read/write by set of concurrence transaction.
Serial Schedule :
A schedule to be equivalent
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
String dbUrl = "jdbc:mysql://your.database.domain/yourDBname";
String dbClass = "com.mysql.jdbc.Driver";
String query = "Select * FROM users";
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection (dbUrl);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
dbtime = rs.getString(1);
System.out.println(dbtime);
} //end while
con.close();
String userName = "username";
String password = "password";
String url = "jdbc:sqlserver://MYPC\\SQLEXPRESS;databaseName=MYDB";
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
Connection conn = DriverManager.getConnection(url, userName, password);
CREATE TRIGGER trg_UpdateTimeEntry
ON dbo.TimeEntry
AFTER UPDATE
AS
UPDATE dbo.TimeEntry
SET ModDate = GETDATE()
WHERE ID IN (SELECT DISTINCT ID FROM Inserted)
Alter PROCEDURE GetstudentnameInOutputVariable
(
@studentid INT, --Input parameter , Studentid of the student
@studentname VARCHAR (200) OUT, -- Output parameter to collect the student name
@StudentEmail VARCHAR (200)OUT -- Output Parameter to collect the student email
)
AS
BEGIN
SELECT @studentname= Firstname+' '+Lastname,
@StudentEmail=email FROM tbl_Students WHERE studentid=@studentid
END
Monday, March 4, 2013
Saturday, January 12, 2013
Midterm Prepare
Attribute : A property of an entity or a relationship type.
Attribute Domain : The set of allowable values for one or more attributes.
Simple Attribute : An attribute composed of a single component with an independent existence.
Composite Attribute : An attribute composed of multiple components, each with an independent existence.
Single-Value Attribute : an attribute that holds a single for each occurrence of an entity type.
Multi-Value Attribute : An attribute that holds multiple values for each occurrence of and entity type.
Derived Attributes : An attribute that represents a value that is derivable from the value of a related attribute or set of attributes, not necessarily in the same entity type.
Composite Key: a candidate key that consists of two or more attributes.
Strong Entity: An entity that is not existence-dependent on some other entity type.
Weak Entity : An entity that is existence-dependent on some other entity type.
Cardinality : Describes the maximum number of possible relationship occurrences for an entity participating in a given relationship type.
Participation : Determines whether all or only some entity occurrences participate in a relationship.
Fan trap: Where a model represent a relationship between entity types, but the pathway between certain entity occurrences is ambiguous.
Chasm traps : Where a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entity occurrences.
Super Class : An entity type that includes one or more distinct sub groupings of its occurrences, which require to be represented in a data model.
Sub Class : A distinct sub grouping of occurrences of an entity type, which require to be represented in a data model.
Specialization : The process of maximizing the differences between members of an entity by identifying their distinguishing characteristics.
Generalization : The process of minimizing the differences between entities by identifying their common characteristics.
Participation Constraint: Determines whether every member in the super class must be participate as a member of a subclass.
Disjoint Constraint : Describes the relationship between members of the subclasses and indicates whether it is possible for a member of a super class to be a member of one or more than one subclass.
Aggregation : Represents a 'has-a' or 'is part of' relationship between entity types, where one represents the 'whole' and the other the 'part'.
Composition : A specific form of aggregation that is represents an association between entities, where there is a strong ownership and coincidental lifetime between the 'whole' and the 'part'.
Attribute Domain : The set of allowable values for one or more attributes.
Simple Attribute : An attribute composed of a single component with an independent existence.
Composite Attribute : An attribute composed of multiple components, each with an independent existence.
Single-Value Attribute : an attribute that holds a single for each occurrence of an entity type.
Multi-Value Attribute : An attribute that holds multiple values for each occurrence of and entity type.
Derived Attributes : An attribute that represents a value that is derivable from the value of a related attribute or set of attributes, not necessarily in the same entity type.
Composite Key: a candidate key that consists of two or more attributes.
Strong Entity: An entity that is not existence-dependent on some other entity type.
Weak Entity : An entity that is existence-dependent on some other entity type.
Cardinality : Describes the maximum number of possible relationship occurrences for an entity participating in a given relationship type.
Participation : Determines whether all or only some entity occurrences participate in a relationship.
Fan trap: Where a model represent a relationship between entity types, but the pathway between certain entity occurrences is ambiguous.
Chasm traps : Where a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entity occurrences.
Super Class : An entity type that includes one or more distinct sub groupings of its occurrences, which require to be represented in a data model.
Sub Class : A distinct sub grouping of occurrences of an entity type, which require to be represented in a data model.
Specialization : The process of maximizing the differences between members of an entity by identifying their distinguishing characteristics.
Generalization : The process of minimizing the differences between entities by identifying their common characteristics.
Participation Constraint: Determines whether every member in the super class must be participate as a member of a subclass.
Disjoint Constraint : Describes the relationship between members of the subclasses and indicates whether it is possible for a member of a super class to be a member of one or more than one subclass.
Aggregation : Represents a 'has-a' or 'is part of' relationship between entity types, where one represents the 'whole' and the other the 'part'.
Composition : A specific form of aggregation that is represents an association between entities, where there is a strong ownership and coincidental lifetime between the 'whole' and the 'part'.
Assignment
1.) What is a database system catalog? List three examples of information included in the database system catalog.
Answer : A database system catalog is also the same with data dictionary is a system that stores names, types and sizes of data items, name of relationships, name of authorized users who have access to data, usage static, security, audit information, redundancy and consistency. For example
1.) Customer service user can access to customer management system.
2.) Customer service user can not access to financial system.
3.) Employee A is work in department A. That is show the relationship between Employee Table with department table.
2.) Describe an application program which exhibits physical data independence.
Answer: Physical data independence refers to the conceptual schema changes in the internal schema for example different organization or storages structures using different storage devices, modifying indexes, or hashing algorithm, should be possible without having changed the conceptual or external schema. Physical data independence is present in most databases and file environment in which hardware storage of encoding, exact location of data on disk,merging of records, so on this are hidden from user.
3.) What is composite attribute of an entity? Give example.
Answer : Composite attribute is an attributes that can be divided to subparts for example we have (first name,middle name,last name) so it can be divided to composite attribute name and if we have attributes (street, city,state,zip) we can divided to composite attribute address.
Please go to check in MS Word file because I have a picture for this answer.
4.) What is cardinality of an entity relationship? Give example.
Answer : Cardinality of an entity relationship status of the relationship between two entities connected by relationships. Cardinality shows a value of relationship. Two entities are connected has a value of 1 to 1 which means one data in first entity associated with a data residing on another entity. There are 1 to N relationship means that one value in the first entity can be linked with a lot of value in the second entity.
Cardinality also describes the maximum number of possible relationship occurrences for an entity participating in a given relationship type.
Please go to check in MS Word file because I have a picture for this answer.
Example
In this diagram explaining how each existing employee must work at least one project but the employee could be on later today must work on many projects. project does not have employees who are assigned for a period of waiting for the project is finished and of course some project has a number of employees.
5.) What is different a weak and a strong entity in a relation? Give example.
Answer :
Strong entity is an entity that is not existence-dependent on some other entity.
Each entity occurrence is uniquely identifiable using the primary key attribute of that entity type. For example we can uniquely identify each member of staff using the staffNo attribute, which is the primary key for the staff entity.
- Weak Entity : An entity that is existence-dependent on some other entity. Each entity can not be uniquely identify using only the attributes associated with that entity. Weak Entity are sometimes referred to as child, dependent, or subordinate entities and strong entity are parent or owner entities. For example
We have strong entity Client(ClientNo,Name(frame,lame),telNo) Weak entity Preference(prefType,maxRent)
There is no primary key for the preference entity. This mean that we can not identify each occurrence of the preference entity using only the attributes of this entity.
6.) Describe conditions for Fan trap and Chasm trap problem to occur in ER Model and how each can be resolved?
Answer:
Fan trap is where a model represents a relationship between entity types, but the pathway between certain entity occurrences is ambiguous. A fan trap may exist where two or more 1:* relationships fan out from the same entity.
Fan Trap: occurs when a “One to Many” join links a table which is in turn linked by another “One to Many” join.
There are two ways to solve Fan trap:
• Creating an alias and applying aggregate awareness function. This is the most effective way to solve the Fan trap problem.
• Using Multiple SQL statements for each measure.
Chasm Trap: occurs when two “Many to one” joins from two Fact tables converge on a single Lookup table.
Can be solved by:
• Creating a Context.
• Using Multiple SQL statements for each measure
• Creating multiple universes (WEBINTELLIGENCE only).
7.) Normalize the relation Register to the highest possible Normal form.
1NF :
Course(Course_ID,Course_Name)
Register(SID,SNAME,Course_ID,BULIDING#,ADDRESS)
2NF :
Course(Course_ID,Course_Name)
Building(Building_Number,Address)
Student(SID,first_name,last_name,sex,date_of_birth,email,tel)
Register(SID,Course_ID,Building_Number)
8. Operations of Vedic Dental Clinic can be described as follows. Each patient is assigned a dentist. A dentist takes care of several patients in a day. Each dentist supervises a crew of dental hygienists who report only to that dentist. Specialized services are performed by dentists only. Regular cleaning and other general services are performed by any dental hygienist among the crew supervised by patient’s dentist.
Identify entities associated with Vedic Dental Clinic, relationships among the entities and provide cardinality for each relationship you have describe [5]
Answer :
Entities are Dentist, Dental hygienist, and Patient
Relationships among the entities:
Dentist to Dental hygienist relationship: 1:N (one-to-many)
Dentist to Patient relationship: 1:N (one-to-many)
Dental hygienist to Patient relationship: M:N(many-to-many)
9. Convert the following ER model to a relational model
Branch(BrID,Address)
Car(VIN#, Model,BrID)
SaleCar(VIN#,BrID,SalePrice)
RentalCar(VIN#,BrID,RentPrice)
RentalCarServiceHistory(VIN#, ServHist)
10. Identify the relational operators O1 and O2 and parameters used in conjunction
with the operators. Can the operators be applied in reverse order ((RO2)O1)
and produce the same output?
• Relational operator O1 is Intersection “∩”.
• Relational operator O2 is Set Difference “-”
Can the operators be applied in reverse order ((RO2)O1)and produce the same output?
Answer: Yes
Answer : A database system catalog is also the same with data dictionary is a system that stores names, types and sizes of data items, name of relationships, name of authorized users who have access to data, usage static, security, audit information, redundancy and consistency. For example
1.) Customer service user can access to customer management system.
2.) Customer service user can not access to financial system.
3.) Employee A is work in department A. That is show the relationship between Employee Table with department table.
2.) Describe an application program which exhibits physical data independence.
Answer: Physical data independence refers to the conceptual schema changes in the internal schema for example different organization or storages structures using different storage devices, modifying indexes, or hashing algorithm, should be possible without having changed the conceptual or external schema. Physical data independence is present in most databases and file environment in which hardware storage of encoding, exact location of data on disk,merging of records, so on this are hidden from user.
3.) What is composite attribute of an entity? Give example.
Answer : Composite attribute is an attributes that can be divided to subparts for example we have (first name,middle name,last name) so it can be divided to composite attribute name and if we have attributes (street, city,state,zip) we can divided to composite attribute address.
Please go to check in MS Word file because I have a picture for this answer.
4.) What is cardinality of an entity relationship? Give example.
Answer : Cardinality of an entity relationship status of the relationship between two entities connected by relationships. Cardinality shows a value of relationship. Two entities are connected has a value of 1 to 1 which means one data in first entity associated with a data residing on another entity. There are 1 to N relationship means that one value in the first entity can be linked with a lot of value in the second entity.
Cardinality also describes the maximum number of possible relationship occurrences for an entity participating in a given relationship type.
Please go to check in MS Word file because I have a picture for this answer.
Example
In this diagram explaining how each existing employee must work at least one project but the employee could be on later today must work on many projects. project does not have employees who are assigned for a period of waiting for the project is finished and of course some project has a number of employees.
5.) What is different a weak and a strong entity in a relation? Give example.
Answer :
Strong entity is an entity that is not existence-dependent on some other entity.
Each entity occurrence is uniquely identifiable using the primary key attribute of that entity type. For example we can uniquely identify each member of staff using the staffNo attribute, which is the primary key for the staff entity.
- Weak Entity : An entity that is existence-dependent on some other entity. Each entity can not be uniquely identify using only the attributes associated with that entity. Weak Entity are sometimes referred to as child, dependent, or subordinate entities and strong entity are parent or owner entities. For example
We have strong entity Client(ClientNo,Name(frame,lame),telNo) Weak entity Preference(prefType,maxRent)
There is no primary key for the preference entity. This mean that we can not identify each occurrence of the preference entity using only the attributes of this entity.
6.) Describe conditions for Fan trap and Chasm trap problem to occur in ER Model and how each can be resolved?
Answer:
Fan trap is where a model represents a relationship between entity types, but the pathway between certain entity occurrences is ambiguous. A fan trap may exist where two or more 1:* relationships fan out from the same entity.
Fan Trap: occurs when a “One to Many” join links a table which is in turn linked by another “One to Many” join.
There are two ways to solve Fan trap:
• Creating an alias and applying aggregate awareness function. This is the most effective way to solve the Fan trap problem.
• Using Multiple SQL statements for each measure.
Chasm Trap: occurs when two “Many to one” joins from two Fact tables converge on a single Lookup table.
Can be solved by:
• Creating a Context.
• Using Multiple SQL statements for each measure
• Creating multiple universes (WEBINTELLIGENCE only).
7.) Normalize the relation Register to the highest possible Normal form.
1NF :
Course(Course_ID,Course_Name)
Register(SID,SNAME,Course_ID,BULIDING#,ADDRESS)
2NF :
Course(Course_ID,Course_Name)
Building(Building_Number,Address)
Student(SID,first_name,last_name,sex,date_of_birth,email,tel)
Register(SID,Course_ID,Building_Number)
8. Operations of Vedic Dental Clinic can be described as follows. Each patient is assigned a dentist. A dentist takes care of several patients in a day. Each dentist supervises a crew of dental hygienists who report only to that dentist. Specialized services are performed by dentists only. Regular cleaning and other general services are performed by any dental hygienist among the crew supervised by patient’s dentist.
Identify entities associated with Vedic Dental Clinic, relationships among the entities and provide cardinality for each relationship you have describe [5]
Answer :
Entities are Dentist, Dental hygienist, and Patient
Relationships among the entities:
Dentist to Dental hygienist relationship: 1:N (one-to-many)
Dentist to Patient relationship: 1:N (one-to-many)
Dental hygienist to Patient relationship: M:N(many-to-many)
9. Convert the following ER model to a relational model
Branch(BrID,Address)
Car(VIN#, Model,BrID)
SaleCar(VIN#,BrID,SalePrice)
RentalCar(VIN#,BrID,RentPrice)
RentalCarServiceHistory(VIN#, ServHist)
10. Identify the relational operators O1 and O2 and parameters used in conjunction
with the operators. Can the operators be applied in reverse order ((RO2)O1)
and produce the same output?
• Relational operator O1 is Intersection “∩”.
• Relational operator O2 is Set Difference “-”
Can the operators be applied in reverse order ((RO2)O1)and produce the same output?
Answer: Yes
Wednesday, December 5, 2012
Chapter 4 Relational Algebra and Relational Calculus
Unary Operation There are two of unary operations. They are selection and projection.
.JPG)
Example : List all staff with a salary greater than 10,000.
Osalary>10000(Staff)
The input relation is staff and the predicate is salary>10000. The selection operation defines a relation containing only those staff tuples with a salary greater than 10000. More complex predicates can be generated using the logical operators ^(And),v (OR ) and ~(NOT)
Projection
IIa1,....,an(R) The projection operation works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.
Example : Produce a list of salaries for all staff, showing only the StaffNo,fName,lName, and salary details.
IIstaffNo,fName,lName,salary(Staff)
Unioin :
R U S The union of two relations R and S defines a relation that contains all the tuples of R, or S or both R and S, duplicate tuples being eliminated. R and S must be union-compatible.
Example :
List all cities where there is either a branch office or a property for rent.
IIcity(Branch) U IIcity(PropertyForRent)
Set difference
R-S The set difference operations defines a relation consisting of the tuples that are in relation R, but not in S. R and S must be union-compatible
IIcity(Branch) - IIcity(PropertyForRent) ( take only different )
Intersection
Selection or Restriction
Opredicate(R) The selection operation works on a single relation R and defines a relation that contains only those tuples of R that satisfy the specified condition (predicate).
Example : List all staff with a salary greater than 10,000.
Osalary>10000(Staff)
The input relation is staff and the predicate is salary>10000. The selection operation defines a relation containing only those staff tuples with a salary greater than 10000. More complex predicates can be generated using the logical operators ^(And),v (OR ) and ~(NOT)
Projection
IIa1,....,an(R) The projection operation works on a single relation R and defines a relation that contains a vertical subset of R, extracting the values of specified attributes and eliminating duplicates.
Example : Produce a list of salaries for all staff, showing only the StaffNo,fName,lName, and salary details.
IIstaffNo,fName,lName,salary(Staff)
Unioin :
R U S The union of two relations R and S defines a relation that contains all the tuples of R, or S or both R and S, duplicate tuples being eliminated. R and S must be union-compatible.
Example :
List all cities where there is either a branch office or a property for rent.
IIcity(Branch) U IIcity(PropertyForRent)
Set difference
R-S The set difference operations defines a relation consisting of the tuples that are in relation R, but not in S. R and S must be union-compatible
IIcity(Branch) - IIcity(PropertyForRent) ( take only different )
Intersection
Monday, December 3, 2012
Chapter 3 Relation Model
1.) Relation Data Structure
- Relation : A relation is a table with columns and rows. Or we can say a primary key field.
- Attribute : An attribute is a named column of a relation.
- Domain : A domain is the set of allowable values for one or more attributes.
- Tuple : A tuple is a row of a relation.
- Degree : The degree is number of attributes or number of fields.
- Cardinality : The cardinality of a relation is the number of tuples it contains.
- Relational database : A collection of normalized relations with distinct relation.
Mathematical Relations:
Cartesian Product
D1 = {2,4} , D2 = {1,3,5}
D = D1 x D2 = { (2,1),(2,3),(2,5),(4,1),(4,3),(4,5) };
n
X Di
i=1
2.) Database Relations
- Relation Schema : A named relation defined by a set of attribute and domain name pairs.
Let A1,A2,....An with domians D1,D2,........,Dn then the set{A1:D1,A2:D2,........,An:Dn}
- Relational Database Schema: A set of relation schema, then we can write the relational database schema, or simply relational schema, R, as : R={R1,R2,.....,Rn}
- Property of Relations:
+ the relation has a name that is distinct from all other relation names in the relational schema.
+ Each cell of the relation contains exactly one atomic (single) value;
+ Each attribute has a distinct name.
+ The value of an attribute are all from the same domain.
+ Each tuple is distinct. There are no duplicate tuples.
+ The order of attributes has no significance.
+ In a relation, the possible value s for a given position are determined by the set, or domain, on which the position is defined. In a table the values in each column must come from the same attribute domain.
+ In a set, no elements are repeated. there are no duplicate tuples.
3.) Relational Keys
- Super key : An attribute, or set of attributes, that unique identified a tuple within a relation.
- Candidate Key : A super key such that no proper subset is a super key within the relation.
- Primary Key : The candidate key that is selected to identify tuples uniquely within relation.
- Foreign Key : An attribute, or set of attributes, within one relation that matches the candidate key of some ( possibly the same ) relation.
Null represents a value for an attribute that is currently unknown or is not applicable for this tuple.
3.) Entity Integrity In a base relation, no attribute of a primary key can be null.
Referential Integrity If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null.
General Constraints Additional rules specified by the users or database administrators of a database that define or constrain some aspect of the enterprise.
4.) Terminology
Base Relation : A named relation corresponding to an entity in the conceptual schema whose tuples are physically stored in the database.
View: The dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not necessary exist in the database but can be produced upon request by particular user at the time of request.
The purpose of Views
- It provides a powerful and flexible security mechanism by hiding parts of the database from certain users. Users are not aware of the existence of any attributes or tuples that are missing from the view.
- It permits users to access data in a way that is customized to their needs, so that the same data can be seen by different users in different ways, at the same time.
- It can simplify complex operations on the base relations.
- A user might need Branch tuples that contain names of mangers as well as the other attributes already in Branch.
- Some members of staff should see staff tuples without salary attribute.
- Attributes may be renamed or the order of attributes changed.
- Some members of staff should see only property records for those properties that they manage.
Updating Views
- Updates are allowed through a view defined using a simple query involving a single base relation and containing either the primary key or a candidate key of the base relation.
- Updates are not allowed through views involving multiple base relations.
- Updates are not allowed through views involving aggregation or grouping operations.
- Relation : A relation is a table with columns and rows. Or we can say a primary key field.
- Attribute : An attribute is a named column of a relation.
- Domain : A domain is the set of allowable values for one or more attributes.
- Tuple : A tuple is a row of a relation.
- Degree : The degree is number of attributes or number of fields.
- Cardinality : The cardinality of a relation is the number of tuples it contains.
- Relational database : A collection of normalized relations with distinct relation.
Cartesian Product
D1 = {2,4} , D2 = {1,3,5}
D = D1 x D2 = { (2,1),(2,3),(2,5),(4,1),(4,3),(4,5) };
n
X Di
i=1
2.) Database Relations
- Relation Schema : A named relation defined by a set of attribute and domain name pairs.
Let A1,A2,....An with domians D1,D2,........,Dn then the set{A1:D1,A2:D2,........,An:Dn}
- Relational Database Schema: A set of relation schema, then we can write the relational database schema, or simply relational schema, R, as : R={R1,R2,.....,Rn}
- Property of Relations:
+ the relation has a name that is distinct from all other relation names in the relational schema.
+ Each cell of the relation contains exactly one atomic (single) value;
+ Each attribute has a distinct name.
+ The value of an attribute are all from the same domain.
+ Each tuple is distinct. There are no duplicate tuples.
+ The order of attributes has no significance.
+ In a relation, the possible value s for a given position are determined by the set, or domain, on which the position is defined. In a table the values in each column must come from the same attribute domain.
+ In a set, no elements are repeated. there are no duplicate tuples.
3.) Relational Keys
- Super key : An attribute, or set of attributes, that unique identified a tuple within a relation.
- Candidate Key : A super key such that no proper subset is a super key within the relation.
- Primary Key : The candidate key that is selected to identify tuples uniquely within relation.
- Foreign Key : An attribute, or set of attributes, within one relation that matches the candidate key of some ( possibly the same ) relation.
Null represents a value for an attribute that is currently unknown or is not applicable for this tuple.
3.) Entity Integrity In a base relation, no attribute of a primary key can be null.
Referential Integrity If a foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or the foreign key value must be wholly null.
General Constraints Additional rules specified by the users or database administrators of a database that define or constrain some aspect of the enterprise.
4.) Terminology
Base Relation : A named relation corresponding to an entity in the conceptual schema whose tuples are physically stored in the database.
View: The dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not necessary exist in the database but can be produced upon request by particular user at the time of request.
The purpose of Views
- It provides a powerful and flexible security mechanism by hiding parts of the database from certain users. Users are not aware of the existence of any attributes or tuples that are missing from the view.
- It permits users to access data in a way that is customized to their needs, so that the same data can be seen by different users in different ways, at the same time.
- It can simplify complex operations on the base relations.
- A user might need Branch tuples that contain names of mangers as well as the other attributes already in Branch.
- Some members of staff should see staff tuples without salary attribute.
- Attributes may be renamed or the order of attributes changed.
- Some members of staff should see only property records for those properties that they manage.
Updating Views
- Updates are allowed through a view defined using a simple query involving a single base relation and containing either the primary key or a candidate key of the base relation.
- Updates are not allowed through views involving multiple base relations.
- Updates are not allowed through views involving aggregation or grouping operations.
Monday, November 26, 2012
CHAPTER 2 DATABASE ENVIRONMENTS
1. External Level : The users’ view of the database. This level
describes that
Part of the database that is relevant to each user.
2. Conceptual Level :
This level
describes what data is stored in the database and relationships among the data.
Conceptual level represents: all entities, their attributes,
and their relationship
-
The constraints on the data
-
Semantic information about the data
-
Security and integrity information
3. Internal Level: This level describes how the data is stored in the database.
- Storage space allocation for data and indexes.
- Record descriptions for storage (with stored sizes for
data items)
- Record replacement.
- Data compression and data encryption techniques.
Schema, Mappings and Instances
The overall description of the database is called database
schema. There are three types of schema external, internal and
conceptual schema.
Data Independence
+Logical
data independence: refers to external schema to changes in the conceptual
schema. Example adds or removes of new entities, attributes or relationship
without having to change existing external schema or having to rewrite the
application program.
+Physical
data independence: refers to the
conceptual schema to changes in the internal schema. Changes to the internal
schema, such as using different file organization or storage structures, using
different storage devices, modifying indexes, or hashing algorithms, should be
possible without having changed the conceptual or external schema.
Database Languages
+ Data
Definition Language (DDL) is a language that allows DBA or
users to describe and name the attributes, and relationships required for the
application, together with associated integrity and security constraints.
+ Data
Manipulation Language (DML) is a language that provides a set of operations
to support the basic data manipulations on the data help in the database. Data
manipulation operations are insertion, modification, retrieval and deletion.
Procedural DML: A language
that allow users tell the system what data is needed and exactly how to
retrieve the data.
Non-procedural DML: A language
that allows the user to state what the data is needed rather than how it is to
be retrieved.
Data Models and Conceptual Modeling
Data Model: An integrated collection of concepts for describing
and manipulating data relationships between data, and constraints on the data
in an organization.
A model is representation of real world objects and events, and their association.It is an abstraction that concentrates on the essential.
Components of data model:
1.) Structural part. consisting of a set of rules according to which database can be constructed.
2.) Manipulative part allow operations insert,delete,update and retrieve data.
3.) Set of integrity constraints to ensure the data is accurate.
Object-Based Data Models
use concepts such as entities, attributes, and relationships. Common types of object-based data model are :
+ Entity-Relationship
+ Semantic
+ Functional
+ Object-Oriented.
Record-Based Data Models
Database consists of number of fixed-format records in different types. Each record type defines a fixed number of fields, each field has fixed length. There are three principle type of record-based logical data model.
+Relational data model grouped into relations and provide a declarative method for specifying data and queries: users directly state what information the database contains and what information they want from it, and let the database management system software take care of describing data structures for storing the data and retrieval procedures for answering queries.
+Network data model
a flexible way of representing objects and their relationships. It is of the schema, viewed as a graph in which object types are nodes and relationship types are arcs
+Hierarchical data model
is a data model in which the data is organized into a tree-like structure. The structure allows representing information using parent/child relationships: each parent can have many children, but each child has only one parent (also known as a 1-to-many relationship). All attributes of a specific record are listed under an entity type.
Physical Data Models
describe how data is stored in the computer, representing information such as record structures, record orderings, and access paths.
Conceptual Modeling or Conceptual database design.
is the process of constructing a model of the information use in an enterprise that is independent of implementation details such as the target DBMS, application programs, programming languages or any other physical considerations.
Functionals of a DBMS
1.) Data Storage, retrieval, and update
A DBMS must have users ability to store, retrieve, and update data in the database.
2.) A User-accessible catalog
A DBMS must have a catalog in which descriptions of data items are stored which is accessible to users.
System catalog stores: names,types,and sizes of data items, name of relationships,name of authorized users who have access to data,usage statistic,security,audit information,redundancy and consistency.
3.) Transition support
A DBMS must have a mechanism which will ensure either that all the updates corresponding to a given transaction are made or that none of them is made.
4.) concurrency control services
A DBMS must have a mechanism to make sure the database is updated correctly when multiple users are updating the database concurrently.
5.) Recovery Services
A DBMS must have a mechanism for recovering the database when the database has damaged in anyway.
6.) Authorization Services
A DBMS must have a mechanism to ensure that only authorized users can access the database.
7.) Support for data communication
A DBMS must be capable of integrating with communication software.
8.) Integrity Services.
A DBMS must have to ensure that both the data in database and changes to the data follow certain rules.
9.) Services to promote data independence
A DBMS must include facilities to support the independence of programs from the actual structure of the database.
10.) Utility Services
A DBMS should provide a set of utility services.
Components of DBMS
.JPG)
Multi-User DBMS Architectures.
1.) Teleprocessing : There is one computer with a single central processing unit (CPU) and number of terminals like pictures.
2.) File-Server Architecture
As the file-server has no knowledge of SQL, the DBMS has to request the files corresponding to the Branch and staff relations from the file-server, rather than just the staff names that satisfy the query.
A model is representation of real world objects and events, and their association.It is an abstraction that concentrates on the essential.
Components of data model:
1.) Structural part. consisting of a set of rules according to which database can be constructed.
2.) Manipulative part allow operations insert,delete,update and retrieve data.
3.) Set of integrity constraints to ensure the data is accurate.
Object-Based Data Models
use concepts such as entities, attributes, and relationships. Common types of object-based data model are :
+ Entity-Relationship
+ Semantic
+ Functional
+ Object-Oriented.
Record-Based Data Models
Database consists of number of fixed-format records in different types. Each record type defines a fixed number of fields, each field has fixed length. There are three principle type of record-based logical data model.
+Relational data model grouped into relations and provide a declarative method for specifying data and queries: users directly state what information the database contains and what information they want from it, and let the database management system software take care of describing data structures for storing the data and retrieval procedures for answering queries.
+Network data model
a flexible way of representing objects and their relationships. It is of the schema, viewed as a graph in which object types are nodes and relationship types are arcs
+Hierarchical data model
is a data model in which the data is organized into a tree-like structure. The structure allows representing information using parent/child relationships: each parent can have many children, but each child has only one parent (also known as a 1-to-many relationship). All attributes of a specific record are listed under an entity type.
Physical Data Models
describe how data is stored in the computer, representing information such as record structures, record orderings, and access paths.
Conceptual Modeling or Conceptual database design.
is the process of constructing a model of the information use in an enterprise that is independent of implementation details such as the target DBMS, application programs, programming languages or any other physical considerations.
Functionals of a DBMS
1.) Data Storage, retrieval, and update
A DBMS must have users ability to store, retrieve, and update data in the database.
2.) A User-accessible catalog
A DBMS must have a catalog in which descriptions of data items are stored which is accessible to users.
System catalog stores: names,types,and sizes of data items, name of relationships,name of authorized users who have access to data,usage statistic,security,audit information,redundancy and consistency.
3.) Transition support
A DBMS must have a mechanism which will ensure either that all the updates corresponding to a given transaction are made or that none of them is made.
4.) concurrency control services
A DBMS must have a mechanism to make sure the database is updated correctly when multiple users are updating the database concurrently.
5.) Recovery Services
A DBMS must have a mechanism for recovering the database when the database has damaged in anyway.
6.) Authorization Services
A DBMS must have a mechanism to ensure that only authorized users can access the database.
7.) Support for data communication
A DBMS must be capable of integrating with communication software.
8.) Integrity Services.
A DBMS must have to ensure that both the data in database and changes to the data follow certain rules.
9.) Services to promote data independence
A DBMS must include facilities to support the independence of programs from the actual structure of the database.
10.) Utility Services
A DBMS should provide a set of utility services.
Components of DBMS
Multi-User DBMS Architectures.
1.) Teleprocessing : There is one computer with a single central processing unit (CPU) and number of terminals like pictures.
Disadvantage of file-server
+ Large amount of network traffice.
+ A full copy of the DBMS is required on each workstation.
+ Concurrency, recovery, and integrity control are more complex because there can be multiple DBMS accessing the same files.
Two Tier and Three Tier Client-Server Architecture
.JPG)
.JPG)
Transaction Processing Monitors.
TP Monitor A program that controls data transfer between clients and servers in order to provide a consistent environment, particularly for online trans-action processing.
Transaction Routing The TP Monitors can increase scalability by directing transaction to specific DBMS.
Transaction Processing Monitors.
TP Monitor A program that controls data transfer between clients and servers in order to provide a consistent environment, particularly for online trans-action processing.
Transaction Routing The TP Monitors can increase scalability by directing transaction to specific DBMS.
Load Balancing: The
TP Monitor can balance client requests across multiple DBMS on one or more
computers by directing client service calls to the least loaded server. It can
dynamically bring in additional DBMS as required to provide necessary
performance.
Funneling in
environment with large number of users sometime users difficult logged on simultaneously
to the DBMS. The TP Monitor can establish connections with the DBMS as the
required, and can funnel user request through these connection.
Increases reliability:
TP Monitor acts as a transaction manager, performing necessary actions to
maintain consistency of the database, with the DBMS acting as a resource
manager. If the DBMS fails, the TP Monitor maybe able to resubmit the
transaction to another DBMS or can hold the transaction until the DBMS become
available again.
.JPG){kind=link}
.JPG){kind=link}
Chapter 1
DBMS Summary:
Chapter 1
1.) File Base System
A
collection of application programs that perform services for the end-users such
as production of reports. Each program defines and mange its own data.
2.) Limitation of the File-Based Approach
- Separation and
isolation of data
- More difficult to access the data
when data is isolated in separate files.
- Difficult processing when data
are in difference files.
- Duplication of Data
- is wasteful. It cost time
and money to enter the data more than once.
- It
takes up additional storage space, again with associated cost. Duplication can
avoid by sharing files.
- Lead
to loss of data integrity. The data is no longer consistent. For example the
payroll and personnel department if a member staff moves house and the change
address is communicated only to personnel and not to Payroll. So the person pay
check will sent to the wrong address.
- Data dependence
Changes of existing
structure are difficult to make. Not flexible and hard to maintain. Because if
changes one it will effect to others.
-Incompatible file
formats
The structure of files is
embedded in the application programs; the structures are dependent on the
application programming languages. For example structure of a file generated by
cobol program maybe different from structure of file generated by c program.
The
incompatibility is files make them difficult to process jointly.
-Fixed
queries/proliferation of application programs.
There was no provision for
security or integrity.
Recovery,
in the event of a hardware or software failure, was limited or non-existent;
Access
to the file was restricted to one user at a time. There is no provision shared
access by staff in the same department.
3.)
Database: A shared collection of logically related data, and a
description of this data, designed to meet the information needs of an
organization.
The database represents the entities, the attributes, and
logical relationships between the entities.
4.) DBMS: A
software system that enables users to define, create, maintain, and control
access to the database.
The DBMS is the
software interacts with user’s application program and the database.
-
Allow user define database by DDL (Data
Definition Language) DDL allow users to specify data types and structures and
the constrains on the data stored in the database.
-
Allow insert, delete, update and retrieve data
from database by DML(Data Manipulation Language). DML use query language the
common query language is SQL.
-
Provide control access to database
o
Security system
o
Integrity system maintains the consistency of
stored data.
o
A concurrency control system allows shared
access of the database.
o
Recovery control system restore the database
o
A user-accessible catalog contains description
of data in the database.
5.) Application
Programs
A computer program that
interacts with the database by SQL statement to the DBMS.
Views
Views
provide security. Views can exclude data that some users should not see.
Views
provide a mechanism to customize the appearance of the database.
Views
can present a consistent.
Component of DBMS
Hardware, Software, Data
(schema, tables, attributes, relationship), People and
Procedures
Log on to the
DBMS
Application
programs
Start & Stop
DBMS
Make backup
copies of database
Handle software
and Hardware failure. Change the structure of table.
Advantage of DBMS
-
Control of data redundancy
-
Data consistency ស្ថិរភាព
-
More information from the same amount of data,
sharing of data
-
Improved data integrity
-
Improved security
-
Enforcement of standards
-
Economy of scale
-
Balance of conflicting requirements
-
Improved data accessibility and responsiveness
-
Increased productivity
-
Improved maintenance through data independence
-
Increased concurrency
-
Improved backup and recovery services.
Disadvantage of DBMS
C Complexity , Size, Cost of DBMS, Additional Hardware cost,cost of conversion,performance, higher impact failure.
Subscribe to:
Comments (Atom)