Week 8 - Enhanced ERM and OO-RDBMS

Question 1

Applications of E-ERM

Answer 1

Engineering databases (CAD, CAM)
Multimedia apps
Data mining and warehousing

Question 2

How to indicate that MANAGER is a subclass of EMPLOYEE

Answer 2

EMPLOYEE ⊃ MANAGER

(class ⊃ subclass)

Question 3

Subclass Entities

Answer 3

They inherit attributes/relationships of superclass entity.

They have their own specific, local attributes and relations.

Question 4

Advantages of type inheritance (using subclass entities)

Answer 4

Avoid describing similar concepts more than once
Add more semantic information to the design

Question 5

Specialization

Answer 5

A top-down process. The process of breaking a superclass into a set of subclasses.
1) Start with a superclass (e.g. employee)
2) Identify attributes of the subclasses
3) Establish relationships specific to subclasses

Question 6

Generalization

Answer 6

The opposite of specialization in that it is a bottom-up process, where you group specific entities into a broader subclass.

Question 7

Constraints in specialization

Answer 7

Rules about how entities are divided

Question 8

Constraint: Disjoint Total

Answer 8

An entity can belong to exactly one subclass
(e.g. a CAR cannot be both a TRUCK and a BIKE)

Question 9

Constraint: DIsjoint Partial

Answer 9

An entity can belong to exactly one subclass or none.
(e.g. A generic VEHICLE might not be a TRUCK or a CAR)

Question 10

Constraint: Overlapping Total

Answer 10

An entity must belong to at least one subclass, possibly multiple.
(STAFF could also be STUDENT)

Question 11

Constraint: Overlapping Partial

Answer 11

An entity can belong to multiple subclasses or none.
(PARTS could be MANUFACTURED or PURCHASED or neither).

Question 12

Multiple inheritance

Answer 12

A subclass with more than one superclass.

Question 13

Union Type, Partial Category, Total Category

Answer 13

idk

Question 14

Single Relation VS Multiple Relation

Answer 14

Multiple:
- Employee(SSN, Name, Address)
- Secretary(SSN , TypingSpeed)
- Technician(SSN, EngType)

Single (disjoint subclasses, use null values, not recommended when lots of specific attributes are defined):
- Employee(SSN, Name, Address, TypingSpeed, EngType)

Question 15

What additional attributes will your relation need in the event of overlapping subclasses?

Answer 15

Boolean fields to represent which subclasses it belongs to, since with overlapping subclasses an entity can belong to more than one subclass at a time.

PART ⊃ MANU_PART, PART ⊃ PURC_PART
so your relation would have additional columns: M_flag, P_flag

Question 16

What would your relations look like in the event of a shared subclass?

Answer 16

teaching_assistant is a specialization of employee and specialization of student. they all share SSN.
EMPLOYEE(SSN, Salary, Position, Rank)
STUDENT(SSN, MajorDept, DegreeProgramme)
TEACHING_ASSISTANT(SSN)

alternatively, you could use boolean flags in employee and student (isTeachingAssistant)

Question 17

Imagine creating a new object type, then creating a table that contains a column of data type ‘person’. What happens when you try to DROP the ‘person’ data type?

CREATE TYPE person AS OBJECT (
SSN NUMBER(9,0),
First_name VARCHAR2(15),
Middle_name VARCHAR2(1),
Last_name VARCHAR2(15),
Address VARCHAR2(60),
Tel_no VARCHAR2(30)
);

Answer 17

You get an error. You can’t do that since another table is using it.

Question 18

What’s a VARRAY?

Answer 18

A datatype. It can hold up to n values.

CREATE TYPE tel_array AS VARRAY(5)
OF varchar2(15);

note: varchar2 is oracle specific.

Question 19

How would you insert into a table with a VARRAY?

Answer 19

INSERT INTO emp
VALUES ( ‘Jake’, tel_varray(‘507-344 7070’,
‘507-344 7171’,
‘612-380-2121’));

Question 20

Nested tables

Answer 20

CREATE TYPE addr_tab AS TABLE
OF Address;

CREATE TABLE emp (
name varchar2(15)
addr addr_tab
NESTED TABLE addrSTORE AS nested_address_table
);

INSERT INTO emp
VALUES(‘john’, addr_tab(Address(‘Business’, ‘121 Warren St’)));

Question 21

Selecting from RDBMS vs Selecting from ORDBMS

Answer 21

– r dbms
SELECT E.FNAME, P.PH_TYPE, P.PH_NO, A.ADDR_TYPE, A.CITY, A.STATE
FROM EMP E, ADDRESS A, PHONE P
WHERE E.EMPID = A.EMPID AND E.EMPID = P.EMPID;

– or dbms
SELECT E.EMPLOYEE.FNAME, E.EMPLOYEE.PHONE, E.EMPLOYEE.ADDRESS
FROM EMP E;

Question 22

Creating procedures in SQL (Oracle)

Answer 22

CREATE OR REPLACE PROCEDURE GetEmployeeDetails (p_employee_id IN NUMBER)
IS
BEGIN
SELECT *
FROM Employees
WHERE EmployeeID = p_employee_id;
END;