Case Study #2: Bautista - De Leon Law Firm

You may download a copy of the second case study via the following link: Case Study #2: Bautista - De Leon Law Firm (Link: http://sdrv.ms/11tL4xU)

Or, you may  copy the content below.
*******************************************************************************

Case Study # 2: Bautista – De Leon Law Firm

The Bautista-De Leon Law firm has been an authority when it comes to handling cases that are sensitive as their main clients are celebrities and politicians. The lawyers under the firm are:

1.      John B. Bautista, ID: 10001,  57 years old, Male

2.      Ross G. De Leon, 10002, 47, F

3.      Raymond M. Dela Cruz, 90562, 25, M

4.      Sarah M. Pena, 56734, 45, F

5.      Jason K. Arioles, 23415, 24, M

6.      Roberto S. Juan, 34126, 45, M

7.      Morrie H. Jover, 90345, 29, M

8.      Jenn Y. Yu, 10006, 35, F

9.      Michelle K. De Leon, 10008, 27,F

For the last few months, Bautista-De Leon had few high profile clients:

1.      Nancy B. Benay, a politician, had asked for political advises from Atty. Dela Cruz and Atty. Pena.

2.      After the election, Mrs. Benay had then asked for Atty. Ross De Leon for regular advice.

3.      Krissy M. Akino, a celebrity, had filed for annulment with James A. Yup with the assistance of Atty. Bautista

4.      Pinky K. De Lion, another celebrity requested a draft for prenuptial agreement with her Fiance, Jake H. Keunca, with Atty. Pena’s assistance.

5.      Mr. Kuenca, on the other hand, had Atty. Arioles’ representation

6.      Pinoy M. Akino, a politician and Krissy’s brother has asked for Atty. Yu’s advice.

7.      Fanny K. Locson, a politician, had been accused of murder. He had sought help from Atty. Juan as Mr. Locson allegedly had been set up.

The firm had always internally classified their cases under three classes: Class A, for celebrity cases; Class B, for cases associated with politics; Class X, for criminal cases; and Class O, for those uncategorized.

Create a database that would record all the transactions in the firm. Then, create a query that would show all the transaction made and 4 queries, each showing cases categorized per classes.

NOTE that there is no need account the earnings of the firm.

DB: Bautista_DeLeon_<your lastname>

Criteria for Scoring:

Proper Database Structure

               Tables

                              Naming Conventions                      15 pts

                              Content                                             10 pts

               Relationship                                                     5 pts

Query

               Transaction                                                      5 pts

               Query per Class                                               10 pts

Mechanics                                                                       5 pts__ 

Total                                                                                 50 pts

Prepared by:

Mr. Rodolfo C. Eregia, Jr.

*******************************************************************************

You may also visit my SkyDrive account for updates on more notes and references. (Link: http://sdrv.ms/18AUHgJ)

Case Study 1: Book ReSale, Inc Transactions

Case Study 1: Book ReSale, Inc System

Mr. John Delgado decided to put up a business on retailing books that are both brand new and already used with the markup of only 10%. So, after renting a spot in a known mall, he started advertising his proposed business to prospected suppliers and clients to great results.

1.      Janet De Castro had a used book, The Devil’s Advocate by Evan Goodling, in good condition sold to Book ReSale for Php 300.50

2.      Clark Henares sells 5 copies of The Notebook by Nicholas Sparks all in good condition for Php 150.00 each

3.      Rubeth Palacios sells her collection of the Percy Jackson series (The Lightning Thief, The Sea of Monsters, The Titan’s Curse, The Battle of the Labyrinth, The Last Olympian) by Rick Riordan for Php 250.00 each, all of whom are in good condition.

4.      Michael de la Cruz sells his copy of The Titan’s Curse for Php 50.00 only as it was in bad condition.

5.      HarperCollins supplies the following brand new books with their corresponding prices and copies:

          1.      The Hunger Games by Suzzane Collins, 350.00, 7

          2.      Catching Fire by Suzzane Collins, 350.00, 7

          3.      Mockingjay by Suzzane Collins, 350.00, 7

          4.      Divergent by Veronica Roth, 400.00,  5

          5.      Insurgent by Veronica Roth, 400.00, 5

          6.      American Gods by Neil Gaiman, 250.00, 3

          7.      The Graveyard Book by Neil Gaiman, 250.00, 3

6.      VisPrint also supplies the following books:

          1.      ABNKKBSNPLAKo by Bob Ong, 100.00, 5

          2.      Bakit Baliktad Magbasa by Bob Ong, 100.00, 5

          3.      Stainless Longganisa by Bob Ong, 150.00, 5

7.      Bryan Guevara bought a copy of Mockingjay. Since he was the first customer, Mr. Delgado gave him a 5% discount.

8.      Jesse Mitchell buys the two copies of The Titan’s Curse.

Assume that you were hired to be a data processing specialist, create a database model for Book ReSale based on the collected data above.

NOTE: In this case study, do NOT take note of the Inventory of Books and the Accounting of Sales. Simply record the transactions in the database.

Solution(in MS Access 2007)(Note: click the smaller download link, not the big one in the ads):

http://www.2shared.com/file/fCqlSGrk/Case_1.html

CCS 2100 Presentations

The following are the links for the first two presentations in our lesson on Fundamentals of Database Design (Note: click the smaller download link, not the big one in the ads):


Chapter 1
http://www.2shared.com/document/QX5YMz7y/9780538469685_PPT_ch01.html

Chapter 2
http://www.2shared.com/document/yIJ0c07_/9780538469685_PPT_ch02.html


Remember that this is only for this time. No notes will be given after the prelim exams.