Mid term Exam

1. Mid term Exam COVERS chapters 1-3, 7-8 Two parts In class Take home part (given on Oct 29th, Due Nov5th in class)

2. System catalogs Information about database • It is automatically created everytime a database is created • It contains tables like: • Systables • Syscolumns • Sysindexes • Sysviews

3. In ORACLE User_catalog • SQL> desc user_catalog; • Name Null? Type • ----------------------------------------- -------- ---------------------------- • TABLE_NAME NOT NULL VARCHAR2(30) • TABLE_TYPE VARCHAR2(11) • Select * from user_catalog;

4. USER_TABLES • User_objects • User_tab_columns • User_views You can query using select statements

5. To get constraints • SQL> select owner, constraint_type • from user_constraints • where table_name ='WROTE'; 2 3 • OWNER C • ------------------------------ - • AGGARWAL P • AGGARWAL R • AGGARWAL R • Note P for primary • R for referential

6. Systables in ORACLEhttp://www.tek-tips.com/viewthread.cfm?qid=1292962&page=3 • there is no such object as systables in oracle. What you are looking for isdba_tables - All tables in system, only visible to administratorsall_tables - All tables in all schema's that the connected user has access to.user_tables - all tables in the connected users schema. BillOracle DBA/Developer

7. Ch 4 Entity Relationship (ER Modeling)

8. Business rules Business rules are precise statements, derived from a detailed description of the organization's operations. When written properly, business rules define one or more of the following modeling components: • entities • relationships • attributes • connectivities • cardinalities • constraints

9. Because the business rules form the basis of the data modeling process, their precise statement is crucial to the success of the database design. because the business rules are derived from a precise description of operations, design's success depends on the accuracy of the description of operations.

10. Examples of business rules are: • An invoice contains one or more invoice lines. • Each invoice line is associated with a single invoice. • A store employs many employees. • Each employee is employed by only one store. • A college has many departments. • Each department belongs to a single college. (This business rule reflects a university that has multiple colleges such as Business, Liberal Arts, Education, Engineering, etc.) • A driver may be assigned to drive many different vehicles. • A client may sign many contracts. • Each contract is signed by only one client.

11. DATA MODEL • Representation of complex real-world . • Goal is to create a DB that is accurate representation of data needs • and real world data relationships • A good DB design begins with a good data model • ..a method for determining "WHAT" data and relationships should be • stored in DB and not "HOW" data will be stored or processed • Hardware/software indep. • ..development is an iterative process

12. THE E-R Model:(P 124) • Translates different data view into a common framework • Defines data processing and constraints requirements • Peter Chen (1976) was the first person to introduce the concept of ER modeling. • An ER model is a detailed logical representation for an organization • ER Building Blocks (p 124)

13. ENTITIES & ATTRIBUTES Entities: • An object of interest • Person • Place • Event • concept • An entity type consists of entity instances Attributes • Properties of an entity • Describes an entity • Attributes have DOMAINS. • Domain is a set of possible values. Two attributes can share the • same domain

14. Derived Attributes: • These are calculated or extracted from other attributes • Ex: total sales • Typically a derived field is not stored in the DB, calculated as needed

15. Relationship: Association between entities • 1:1 • 1:m • m:n • Unary...within itself • Binary..with two entities • Ternary..with three entities Unary (recursive): Entity has a relationship to itself • 1:1 • 1:m • m:n

16. Super & sub type entities (used extensively in Object oriented systems) • One challenge is to RECOGNIZE and represent entities that are • almost the same, i.e., the share some properties but also have • distinct properties

17. Ex: COMPUTER • Mainframe • Minicomputer • personal • EMPLOYEE • Full time • Part time Relationship: • IS-A super, sub entity relationships are of the type “IS A”; • Ex: full time employee “IS AN” employee • HAS-A: between two entities..STUDENT “HAS A” relationship with CLASS

18. Inheritance • All attributes of a super type become attributes of the subtype • Each attribute should be located at the highest level in the hierarchy

19. CARDINALITY (useful in getting approximate database size) (MIN and Max) Number of instances of one entity that can be associated with each instance of the other entity • Place the appropriate number beside the entity • Minimum • Maximum Student <_------_>class (1,6) (1,40) • student _-------_> major • patient_------_> pat_history

21. Database name: Ch03_BeneCo Table name: EMPLOYEE Table name: BENEFIT Table name: JOB Table name: PLAN Draw the ERD

22. Example if you were to develop an ER model for a video rental store, you would note that tapes can be rented more than once and that customers can rent more than one tape.

25. Q1/136 Entities • COMPANY • DEPARTMENT • DEPENDENT • EMPLOYMENT HISTORY

26. Q2/p136