Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Case Study for the Lecture of Database System by tut53443

VIEWS: 59 PAGES: 14

									               Case Study for the Lecture of Database System



Build software for the case study listed below. Students also have to submit a report
explaining the software requirements and design.


1. A database used in an order-entry system is to contain information about customers,
   items, and orders. The following information is to be included.
    For each customer:
            Customer number (unique)
            “Ship-to” addresses (several per customer)
            Balance
            Credit limit
            Discount
    For each order:
            Heading information: customer number
                                    ship-to address
                                    date of order
            Detail lines (several per order): item number
                                              quantity ordered
    For each item:
            Item number (unique)
            Manufacturing plants
            Quantity on hand at each plant
            Stock danger level for each plant
            Item description
   For internal processing reasons a “quantity outstanding” value is associated with each
   detail line of each order. This value is initially set equal to the quantity of the item
   ordered and is (progressively) reduced to zero as (partial) shipments are made. Design
   a database for this data.

2. Build a database which is to contain information concerning sales representatives,
   sales areas, and products. Each representative is responsible for sales in one or more
   areas; each area has one or more responsible representatives. Similarly, each
   representative is responsible for sales of one or more products, and each product has
   one or more responsible representatives. Every product is sold in every area,
   however, no two representatives sell the same product in the same area. Every
   representative sells the same set of products in every area for which that
   representative is responsible.
3. INDUSTRIAL PLACEMENT. Some British universities offer four-year sandwich
   degrees in particular subjects. A sandwich degree is so-called because it sandwiches
   one year of industrial experience between three years of teaching. The industrial
   placement officer of your university department wishes to set up a database to store
   details of students on their industrial placement year and students currently
   undergoing interviews with companies and organizations for placement. The officer
   currently records the following details about students currently on placement:
   student‟s name, gender, address and date of birth as well as details of the student‟s
   current employer. Each student is also given a supervisor for the year. Each
   supervisor is required to make three trips to an employer during the placement year to
   visit a student. Details held about each supervisor are: name, room number email
   address and telephone extension. Details of students currently undergoing interviews
   for placement are quite complex. Currently, curriculum vitae have to be prepared by
   each student. A list of possible placements is produced by the placement officer.
   Students have to select three possible placements for which they are prepared to be
   interviewed. Details of each interview needs to be held on the database, particularly
   the result as to whether the student is offered a place with the company or
   organization. Students are obliged to accept the first offer of a place they receive.

4. EasyHire Ltd. Is a nationwide car hire company. Easyhire maintains two types of
   organizational unit: depots and hire-points. Depots are places where cars are held and
   maintained. Hire-points are places where customers hire cars. Any one hire-point has
   access to many depots. Each depot may supply cars for many different hire-points.
   Customers pick up cars from depots and return cars to depots. Customers have to pick
   up cars from a specified depot but may return cars to any depot of their choice.
   Employees of EasyHire are categorized as either hire-point or depot employees.
   Depot employees remain at a particular depot. However, hire-point employees
   frequently move between hire-points. Each hire-point employee has a fixed group of
   hire-points at which he or she may be resident. The requirement is to build a database
   systems that keeps track of where cars are being held, where cars have been hired
   from, who has hired cars and the movement of employees.

5. Cinema Land is a company which owns a number of cinemas in the UK. They require
   a corporate database to record details of cinemas, venues and takings. Each cinema
   complex is given a unique code. Other attributes of a cinema include the cinema‟s
   name, its seating capacity, the number of employees, its location and its manager.
   Cinemas show a number of films over a season. The company currently needs to
   know which films are showing in which cinemas. Also, they need to know what films
   have been shown at what cinemas. A venue is a showing of a given film at a given
   cinema. Each venue has a start date and an end date. The company wishes to record
   the entire takings for each venue and the total number of people attending each venue.
6. INFANT IMMUNISATION. A system is required to record details of infant
   immunization in a health region. Every infant in the region is required to have a
   course of general vaccinations against diseases such as whooping cough and
   diphtheria. Patients are identified by a unique NHS number and details such as the
   name, date of birth and NHS number of the parent or guardian of the infant are also
   recorded. Each vaccination is for a single vaccination type such as mumps, rubella,
   etc., and is given to a single patient. However, every infant is given a number of
   booster injections of certain vaccination types at periodic intervals. The date of each
   vaccination is recorded. Vaccination is given by General Practitioners (Gps). A
   general practice usually has many GPs. Each GP works for only one general practice
   and usually has many patients on his or her list. Each infant is on the list of only one
   doctor. Practices are identified by practice names and GPs are identified by unique
   GP numbers. The name of each doctor and the number of vaccination patients on
   each doctor‟s list also needs to be recorded. Besides general update operations, the
   following processes must be supported by the system: The production of appointment
   letters; A work-list by General Practice for a given week; An audit list indicating the
   number of vaccinations by type conducted during the previous six-month period.
7. RESEARCH DATABASE. The university has created a number of structural
   divisions to enable it to manage research. Research units constitute collections of staff
   working in a common research area such as Information Systems, Software
   Engineering, etc. Research centers are aggregations of research units that roughly
   correspond to the units of assessment created by the research assessment exercise
   (RAE). Both research units and research centers are headed by one member of
   academic staff. The following forms of research output are used to assess the
   performance of research units and research centers: publications (journal papers,
   conference papers, professional articles, books, chapters in books), research grants
   (submitted and successful), and research students (registered, completed). Members
   of academic staff at the university author publications and submit or hold research
   grants. A member of staff can author more than one publication and submit/hold more
   than one research grant. A publication can be authored by more than one member of
   staff and a research grant can have more than one applicant. Research students are
   enrolled by an academic department or school and are supervised by one or more
   members of academic staff. Members of staff may supervise more than one research
   student. In terms of research grants, the system needs to record: the title of the grant,
   the applicant(s)/holder(s), the funding body, the total value of the grant submitted, the
   total value of the grant awarded, date grant submitted, data grant awarded. In terms of
   research students, the system needs to record: enrolment number, name of student,
   academic department, date enrolled, date registered, type of research degree (Mphil,
   MPhil/PhD, and PhD), supervisor(s), date transferred, result of transfer, date
   examined, and results of examination. A minimal set of staff details need to be
   recorded in the system: payroll number, name, title, department/school.




8. TELEPHONE SUBSCRIPTION. A telephone subscriber may have one or more
   installations at his or her place of subscription. Each subscriber is identified by a
   unique subscriber number. The name and address of the subscriber is also recorded as
   well as the number of installations. The date of the last payment should be recorded
   against each subscriber. Associated with each installation there is usually at least one,
   and possibly several handsets supplied by the telephone company. A handset is
   identified by a unique serial number. Other attributes are color and type. An
   increasing number of handsets are now provided by subscribers themselves. In this
   case the details are not recorded.


9. Build database for an education which contains information about an in-house
   company education training scheme. For each training course, the database contains
   details of all prerequisite courses for that course and all offerings for that course, and
   for each offering it contains details of all teachers and all student enrolments for that
   offering. It also contains information about employees.
10. Build a university database for the scheduling of classrooms for final exams. This
    database could be modeled as the entities exam, course, and room.


11. Build database for a classical music collection consisting of CDs and VCDs, that will
    let we find which recordings we have for a specific composer (e.g. Sibelius) or
    conductor (e.g. Simon Rattle) or soloist (e.g. Arthur Grumiaux) or orchestra (e.g. the
    NYPO).

12. SQUASH LADDER. You are required to build a small database system to record
    details for a squash ladder. The ladder is divided into a number of divisions. Each
    division has a number of positions. Each squash game has two players and one result.
    A result is made up of two scores, one for each participating player. A player can play
    many games but only with players in the same division. A player can occupy only one
    position in the squash ladder at any one time. Players are ranked in positions in terms
    of their total scores. The system should also record the name, address, telephone
    number and age of each player.
Soal ujian Sistem Basis Data
Dosen : Teguh Bharata Adji

1. Consider an E-R diagram of a banking database system as follows:


                  name          address                  acc_number                balance



        cust_id




                         customer                                      account
                                                having



                                                                       belonging



                                                                        branch



                                                                                             assets


                                                         branch_name               city


    a. Construct appropriate schema diagram.
    b. Construct appropriate data dictionary.

2. Consider the diagram schema of the banking database system you have already
   constructed. Write the appropriate SQL queries to do the following:
    a. Find the address of the customer whose name is John.
    b. Find all of customer‟s name and their acc_number.
    c. Find all of customer‟s name and their balance.
    d. Find how many customers does the Brighton branch have?
    e. Find all of customer‟s name and address having account at any branches located
       in Brooklyn?

3. Consider a database for a classical music collection consisting of CDs and VCDs, that
   will let we find which recordings we have for a specific composer (e.g. Sibelius) or
   conductor (e.g. Simon Rattle) or soloist (e.g. Arthur Grumiaux) or orchestra (e.g. the
   NYPO).
    a. Construct appropriate E-R diagram. Note that too many attributes is unnecessary.
    b. Explain how a trigger (together with application program such as Delphi) can be
       used to give an alert of a critical CDs/VCDs stock (i.e. 5 units for every title). You
       must implement the trigger but not necessary for the application program.
Soal ujian Sistem Basis Data

Dosen : Teguh Bharata Adji
Waktu : 2 jam


1. Explain the distinctions among the terms primary key, candidate key, super key, and
   foreign key by using any diagram schema.

2. Consider a database used to record the marks that students get in different exams of
   different course-offerings.
    a. Construct an E-R diagram along with the diagram schema that models exams as
       entities, and uses a ternary relationship, for the above database.
    b. Construct an alternative E-R diagram along with the diagram schema that uses
       only a binary relationship between students and course-offerings. Make sure that
       only one relationship exists between a particular student and course-offering pair,
       yet you can represent the marks that a student gets in different exams of a course
       offering.

3. Consider the diagram schema of the banking database system below. Write the
   appropriate relational-algebra queries to do the following:
    a. Find the address of the customer whose name is John.
    b. Find all of customer‟s name and their acc_number.
    c. Find all of customer‟s name and their balance.
    d. Find how many customer name does the Brighton branch have?
    e. Find all of customer‟s name and address having account at any branches located
       in Brooklyn?

Branch                Account                Depositor             Customer
  Branch-name           Acc-number             Cust-id               Cust-id
                                               Acc-number
  Branch-city           Branch-name                                  Cust-name
  Assets                Balance                                      Cust-address
Soal ujian Sistem Basis Data

Dosen : Teguh Bharata Adji
Waktu : 2 jam


1. Consider the insurance database of the figure below, where the primary keys are
   underlined. Construct the following SQL queries for this relational database.
   a. Find the total number of people who owned cars that were involved in accident in
      1989.
   b. Find the number of accident in which the cars belonging to “John Smith” were
      involved.
   c. Add a new accident to the database; assume any values for required attributes.
   d. Delete the Mazda belonging to “John Smith”.
   e. Update the damage amount for the car with license number “AABB2000” in the
      accident with report number “AR2197” to $3000.

              person (driver-id, name, address)
              car (license, model, year)
              accident (report-number, date, location)
              owns (driver-id, license)
              participated (driver-id, license, report-number, damage-amount)

2. SQL allows a foreign-key dependency to refer to the same relation, as in the
   following example:

              create table manager
                      (employee-name char(20) not null,
                       manager-name char(20) not null,
                       primary key employee-name,
                       foreign key (manager-name) references manager
                                                  on delete cascade)

    Here, employee-name is a key to the table manager, meaning that each employee has
    at most one manager. The foreign-key clause requires that every manager also be an
    employee. Explain exactly what happens when a tuple in the relation manager is
    deleted.

3. Write an assertion for the bank database to ensure that the assets value for the
   Perryridge branch is equal to the sum of all the amounts lent by the Perryridge
   branch. Note: use bank database schema as used in the book database system
   concepts for questions number 21 and 22.

4. Write an SQL trigger to carry out the following action: On delete of an account, for
   each owner of the account, check if the owner has any remaining accounts, and if she
   does not, delete her from the depositor relation.
Database System Exam for Weekend Class
Lecturer: Teguh Bharata Adji

1. Consider an E-R diagram of a banking database system as follows:

                name          address                   acc_number               balance



      cust_id




                       customer                                      account
                                           having



                                                                     belonging



                                                                      branch



                                                                                           assets


                                                    branch_name                  city


   a. Construct appropriate schema diagram.
   b. Construct appropriate data dictionary.

2. Consider the diagram schema of the banking database system you have already
   constructed. Write the appropriate SQL queries to do the following:
   a. Find the address of the customer whose name is John.
   b. Find all of customer‟s name and their acc_number.
   c. Find all of customer‟s name and their balance.
   d. Find how many customers does the Brighton branch have?
   e. Find all of customer‟s name and address having account at any branches located
      in Brooklyn?

3. Consider the diagram schema below. If all of the fields have the data type of
   varchar(9), then calculates the size in bytes of all records read by the computer‟s
   RAM to perform the query of “select Field1, Field6, Field7 from Table2, Table3
   where Table2.Field4= Table3.Field4”.

Table1                  Table2             Table3
  Field1                  Field1               Field4
                          Field4
  Field2                                       Field5
  Field3                  Field7               Field6
Database System Exam for Regular Class
Lecturer : Teguh Bharata Adji



1. Consider the diagram schema below. If all of the fields have the data type of
   varchar(9), then calculates the size in bytes of all records read by the computer‟s
   RAM to perform the query of “select Field1, Field6, Field7 from Table2, Table3
   where Table2.Field4= Table3.Field4”.

Table1                Table2                      Table3
  Field1                    Field1                  Field4
                            Field4
  Field2                                            Field5
  Field3                    Field7                  Field6



2. Consider an E-R diagram as follows:



                                     1                 N
                  Student                Having              Mobile_phone




   a. Construct appropriate schema diagram.
   b. Construct appropriate data dictionary.
   c. If the attributes of Student = (studentno, birthdate, address) and for Mobile_phone
      = (imei_no, brand, type), then write the appropriate SQL query to find the birth
      date of students who have mobile phone of Nokia brand, and
   d. Write the appropriate SQL query to find how many students who have mobile
      phone of N3105 type.
   e. If all of the fields have data type of varchar(9), then calculates the size in bytes of
      all records read by the computer‟s RAM to perform the query of “select
      studentno, address, imei_no from Student, Having where Student.studentno=
      Having.studentno”.

3. By considering the above E-R diagram, then:
   a. Write the appropriate XML document.
   b. Write the appropriate XPath expression to find all of student‟s birth date.
   c. Write the appropriate XPath expression to find all of Student element who have
      more than one mobile phone.
Database System Mid Test (3 hours)
Lecturer: Teguh Bharata Adji




1. Choose one of the small business system listed below:

    My shop (drug / convenient store), or
    My rental (hotel‟s room / car / motor-bike / CD-software / comic rental), or
    My clinic

   Suppose that you are the owner of the business and you have to define your own
   problem definition of the business system. Write all of the steps to develop a database
   system that will solve the problem.

   Items for Score:

    Problem Definition
    Requirements
    Design (E-R Diagram, Normalisation, DD, Tables) => more complex (explaining
     composite attribute, partial relationship, weak entity, specialization, aggregation,
     and so on) are better
    Testing (give examples of SQL commands for testing the correctness of the DBD)
    Fulfilling good Software Engineering (by using Use Case and so on)
    Ability to answer questions from other groups
Answer:

Question1.

Select …
From Table2, Table3 -> query which involve 2 tables
Where …

Thus computer only read “cross product” Table2 X Table3.
If M = Table2 total records
& N = Table3 total records
Then Table2 X Table3   = (M x N) x (Table2 total characters + Table3 total characters)
                       = (M x N) x (3 field x varchar(9) + 3 field x varchar(9))
                       = (M x N) x (3 field x 10 bytes + 3 field x 10 bytes)
                       = 60 (M x N) bytes

Question2.

[a]

Student                             Mobile_phone
     studentno                        imei_no

     birthdate                        studentno
     address                          brand
                                      type


or



          studentno    birthdate         address




          imei_no      studentno         brand            type



[b] Data dictionary

Table Student
Field name                         Data types                         Comment
studentno                          Char(7)                            PK
birthdate                          Date or datetime
address                            Varchar(30)
Table Mobile_phone
Field name                     Data types                  Comment
imei_no                        Char(7)                     PK
studentno                      Char(7)                     FK
brand                          Varchar(15)
type                           Varchar(15)

[c]

Select Student.birthdate
From Student, Mobile_phone
Where Student.studentno = Mobile_phone.studentno
And brand = „Nokia‟;

Or

Select Student.birthdate
From Student (natural/inner/outer) join Mobile_phone on Student.studentno =
Mobile_phone.studentno
Where brand = „Nokia‟;

[d]

Select distinct count (studentno)
From Mobile_phone
Where type = „N3105‟;
Question3.

[a]

<cellular>
        <Student studentno = “S_1”>
               <birthdate> 20 Sept 1969 </birthdate>
               <address> Gendeng GK IV/642 </address>
        </Student>
        <Student studentno = “S_2”>
               <birthdate> … </birthdate>
               <address> … </address>
        </Student>
        <Mobile_phone imei_no = “M_1” owner = “S_1”>
               <brand> Nokia </brand>
               <type> N3105 </type>
        </Mobile_phone>
        <Mobile_phone imei_no = “M_2” owner = “S_1”>
               <brand> … </brand>
               <type> … </type>
        </Mobile_phone>
</cellular>

[b] /cellular/Student/birthdate/text()

[c] /cellular/Student[Mobile_phone/count()>1]/text()

								
To top