final prepare

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

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'. 

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