3.17

{College(College_name,start_date, end_date, {degrees(degreeName, month, year)}, {transcript(courseName, semester, year, grade)})}

3.23

a. Non-weak entity types: BANK, ACCOUNT, LOAN, CUSTOMER

b. Weak Entity: BANK-BRANCH, the partial key is: BranchNo, the identifying relationship is: BRANCHES.

c. Each entity instance in BANK-BRANCH entity type should be associated with one and only one entity instance in the entity type BANK. A BANK entity instance must have at least one BANK-BRANCH entity instance be associated with it. The BranchNo of BANK-BRANCH must be unique with in a particular BANK.

d.

e.

1. Each customer has a social security number, which is unique, and the database needs to keep track of the name, address, and phone of each customer as well.
2. Each account has a unique account number. For each account, the database keeps track of the Balance and account type.
3. A loan has a unique loan number. The amount of the loan and the type of the loan are required to be stored in the database.
4. The branch number of each bank branch is unique within a particular bank. Additionally, the database also keeps track of the address of each bank branch.
5. A bank has a unique bank code, and the name and address of the bank are also stored in the database.
6. A bank branch must belong to one and only one particular bank. However, a bank has at least one but may have many branches.
7. A customer may have many accounts, and a joint account is allowable. So are the loans.
8. A loan must be carried by exactly one particular bank branch.

4.20

Assumptions: Omitted

5. Map the Company ER schema in Figure 3.2 into ODL description

Class Employee (Key SSN)

{
attribute String SSN;
attribute struct NameStruct {String Fname, char Minit, String Lname} Name;
attribute enum Gender {Male, Female} Sex;
attribute String Address;
attribute float Salary;
attribute Date Bdate;
attribute Date StartToManage;

relationship Department Works_for inverse Department::Has_employee;
relationship Set<WorkingHours> Emp_Hours inverse WorkingHours::Hours_Emp;
relationship Department Manages inverse Department::Has_Manager;
relationship Employee Supervised_by inverse Employee::Supervise;
relationship Set<Employee> Supervise inverse Employee::Supervised_by;
relstionship Set<Dependent> has_dependent Dependent::Dependent_of;
};

Class Department (Key Name)

{
attribute String Name;
attribute Set<String> Locations;
attribute String Number;
relationship Set<Employee> Has_employee inverse Employee::Works_for;
relationship Employee Has_Manager inverse Employee::Manages;
relationship Set<Project> Controls inverse Project::Controled_by;
integer NumberOfEmployee() // This is a method

};

Class WorkingHours
{
attribute integer Hours;
relationship Employee Hours_Emp inverse Employee::Emp_Hours;
relationship Project Hours_Proj inverse Project::Proj_Hours;
};

Class Project (Key Name)
{
attribute String Name;
attribute String Number;
attribute String Location;
relationship Set<Working_hours> Proj_Hours inverse WorkingHours::Hours_Proj;
relationship Department Controled_by inverse Department::Controls;
};

Class Dependent
{
attribute String Name;
attribute enum Gender{Male, Female} Sex;
attribute Date Birthdate;
attribute String RelationshipWithEmp;
relationship Employee Dependent_of inverse Employee::Has_dependent;
}