3380 Ex 2

• ~ Conceptual Model ~ Designer's view
• High Abstraction "ER" HW/SW Indep

• ~ Internal Model ~ DBMS view
• Med Abstraction "Relational" HW Indep SW Dep

• ~ Physical Model
• Low Abstraction "Network Hierarchial" HW/SW Dep

• Structured
• Query
• Language

~They support simple powerful querying of data

~They can be written intuitively

~DBMS is responsible for efficient evaluation

~a set of relations

• ~made up of two parts:
•   -Schema (specifies name of relation, name and type of each column)
•   -Instance (table with rows and columns)

~has a domain = type

~condition that must be true for any instance of the database; e.g. domain constraints:

•   -IC are specified when schema is defined
•   -IC are checked when Relations are modified

~ICs are based upon the semantics of the real-world enterprise

~one that can be identified uniquely only by considering the primary key of another entity

~a view is just a relation, but we store a definition, rather than a set of tuples

• relation-list: list of relations referred to
• target-list: list of attributes in relations
• qualification: comparisons etc

• 1. Compute join of relation-list (bowtie)
• 2. Discard resulting tuples if fail qualifications (sigma)
• 3. Delete attributes not in target-list (pi)

S.sailors ETC.

• LIKE - used for string matching
• _ - one character
• % - zero or more arbitrary characters
• DISTINCT - removes duplicates
• UNION
• INTERSECT
• EXCEPT
• IN
• [op] ANY/ALL
• EXISTS
• NOT - in front of IN or EXISTS

GROUP BY - must be subset of relation-list and must be a single value per group

LEGAL:

• SELECT S.sname, S.age FROM Sailors S
•      WHERE S.age = (SELECT MAX (S2.age) 
•           FROM Sailors S2);

• SELECT S.rating, MIN(S.age) FROM Sailors S
•      WHERE S.age >=18
•      GROUP BY S.rating
•      HAVING COUNT (*) > 1;

~A general constraint, to check entries being made

~Constraint over multiple relations is wrong, should create an assertion instead

~procedure that starts automatically if specified changes occur to the DBMS

• 1. EVENT - activates the trigger
• 2. CONDITION - tests wether trigger should run
• 3. ACTION - what happens if it runs

TRIGGERS CAN: keep counts, alert users to events, keep stats etc.

~more operational, very useful for representing execution plans

• ~lets users describe what they want rather than how to get it
• :Tuple relational calculus
• :Domain relational calculus

GUI based on DRC

• P. print
• I. insert
• D. delete

Queries that return infinite tuples

Any safe query in one language is safe in another.

ODBC - open database connectivity, drivers provided by database vendors so applications can access their respective databases's data sources

OLE DB - object linking & embedding database, is a microsoft low level API (application program interface) for access to different data sources

ADO.NET - ActivX Direct Objects, microsoft consistent access to data sources as well as data sources exposed through OLE DB and XML

JDBC - java.sql package provided by sun which is a standard library for accessing relational databases

stored procedure. can open a cursor, repeatedly fetch a tuple, then move the cursor until all tuples have been reached

• EXEC SQL DECLARE sinfo CURSOR FOR
• select blah.....

program executed through a single SQL statement

executed in the process space of the dbms server

avoids tuple at a time method of cursors

do not have to be written in java

• CallableStatement cstmt = con.prepareCall("{CALL ShowSailors}");
• ResultSet rs = cstmt.executeQuery();
• while rs.next()
• {
• ...
• }

• #sql iterator ShowSailors(...);
• ShowSailors showsailors;
• #sql showsailors = {CALL ShowSailors};
• while (showsailors.next())
• {
• ...
• }

• naming schema (http)
• host computer  (www.what.com)
• name of the resource (~index.html)

• 1. client sends request to server
• 2. server recieves request and replies
• 3. client recieves reply, makes new requests

stateless, no sessions. each message is self contained

a heirarchy of user defined tags called elements with attributes and data

is a schema for xml data, tells what elements and attributes are optional and what is not

• M: Data management (database system)
• -duh

• C: (Application Server)
• -implements buisness logic; maintains steps between states of workflow; accesses different data management systems

• V: Presentation (client program (web browser))
• -primary interface of user, must adapt to many different displays

• A TO_DATE()
• B SYSDATE()
• **C DATE()
• D TODAY()

• A. subtype discriminator
• B. inheritance
• **C. specialization hierarchy
• D. entity supertype

• A. unique values
• B. nonintelligent
• C. preferably single-attribute
• **D. security complaint

• A. Specialization
• **B. Generalization
• C. Partial completeness
• D. Total completeness

• A. Rapid development at manageable costs
• **B. Need to support different software platforms
• C. Common and simple user interface
• D. Location independence