Queries in the Enrayle's Party Planning Lesson

CREATE TABLE Customer
(
CustID int PRIMARY KEY,
Lastname varchar(50) NOT NULL,
Firstname varchar(50) NOT NULL,
Middlename varchar(50) NOT NULL,
CustAddress varchar(50),
ContactNumber varchar(50),
Email varchar(50)
);

CREATE TABLE Party
(
PartyID int PRIMARY KEY,
PartyName varchar(50) NOT NULL,
PartyDate varchar(50) NOT NULL,
Nature_ofParty varchar(50),
PartyAddress varchar(50) NOT NULL,
CustID int NOT NULL
);

CREATE TABLE Food
(
FoodID int PRIMARY KEY,
FoodName varchar(50) NOT NULL,
FoodType varchar(50)
);


CREATE TABLE Menu
(
MenuID int PRIMARY KEY,
PartyID int NOT NULL,
FoodID int NOT NULL,
No_Servings int NOT NULL
);

INSERT INTO Customer
(CustID, Firstname, Lastname, Middlename,
CustAddress, ContactNumber, Email)
VALUES
('1','Jhosalie','Ardena','Uy',
'Pavia','12345','jhosaardena@yahoo.com'),
('2','RK','Astronomia','Eusuya',
'Roxas','54321','RKEusuya@gmail.com'),
('3','Striker','Aspero','Macasio',
'Pasig','67891','strikermacasio@yahoo.com');

INSERT INTO Party
(PartyID, PartyName, PartyDate, Nature_ofParty,
PartyAddress, CustID)
VALUES
('1','Francis 28th

Birthday','11/01/2014','Bday',
'Zarraga','2'),
('2','Death Anniversary','12/24/2014','Burial',
'Leganes','3'),
('3','Jing Jing Bday','1/1/2015','Bday',
'Jaro','2');

INSERT INTO Food
(FoodID, FoodName, FoodType)
VALUES
('1','Solar Dried Fish','Snack'),
('2','KBL','Dish'),
('3','Menudo','Dish');

SELECT * FROM Food;

INSERT INTO Menu
(MenuID, FoodID, PartyID, No_Servings)
VALUES
('1','1','1','200'),
('2','3','2','150'),
('3','3','3','50');

SELECT * FROM Menu;

SELECT Lastname, Firstname, Middlename FROM

Customer;

SELECT FoodName, FoodType FROM Food;

SELECT Food.FoodName, Food.FoodType,

Menu.No_Servings
FROM Party
INNER JOIN Menu
ON Party.PartyID = Menu.PartyID
INNER JOIN Food
ON Menu.FoodID = Food.FoodID
WHERE Party.PartyName = 'Jing Jing Bday'



SELECT Party.PartyName, Party.PartyAddress
FROM Customer
INNER JOIN Party
ON Customer.CustID = Party.CustID
WHERE Customer.Lastname = 'Astronomia';

SELECT Customer.Lastname FROM Food
INNER JOIN Menu
ON Food.FoodID = Menu.FoodID
INNER JOIN Party
ON Menu.PartyID = Party.PartyID
INNER JOIN Customer
ON Party.CustID = Customer.CustID
WHERE Food.FoodName = 'Menudo';

SQL Basics

CREATE TABLE Students
(
StudentID int PRIMARY KEY NOT NULL,
StudName varchar(50) NOT NULL
);

CREATE TABLE Books
(
BookID int PRIMARY KEY,
Title varchar(50) NOT NULL,
Author varchar(50)
);

CREATE TABLE Borrow
(
BorrowID int PRIMARY KEY,
StudentID int NOT NULL,
BookID int NOT NULL
);

INSERT INTO Students
(StudentID, StudName)
VALUES
('1','Alquisada');

INSERT INTO Students
(StudentID, StudName)
VALUES
('2','Ardena'),
('3','Suner'),
('4','Eusuya'),
('5', 'Arca');

SELECT * FROM Students;

SELECT StudName FROM Students;

INSERT INTO Books
(BookID, Title, Author)
VALUES
('1','Harry Potter','JK Rowling');

INSERT INTO Books
(BookID, Title, Author)
VALUES
('2','Percy Jackson','Rick Riordan');

SELECT * FROM Books;

INSERT INTO Borrow
(BorrowID, StudentID, BookID)
VALUES
('1','1','1'),
('2','1','2'),
('3','2','1');


SELECT * FROM Borrow;

SELECT
Students.StudName, Books.Title, Books.Author
FROM Students
INNER JOIN Borrow
ON Students.StudentID = Borrow.StudentID
INNER JOIN Books
ON Borrow.BookID = Books.BookID;

SELECT
Students.StudName, Books.Title, Books.Author
FROM Students
INNER JOIN Borrow
ON Students.StudentID = Borrow.StudentID
INNER JOIN Books
ON Borrow.BookID = Books.BookID
WHERE Students.StudentID = '2'
;
DROP TABLE Books;

ALTER TABLE Books
ALTER COLUMN Title varchar(50);

ALTER TABLE Books
ADD Book_Desc varchar(100);

ALTER TABLE Books
DROP COLUMN Book_Desc;

UPDATE Books
SET Title = 'Title 2', Author = 'dfgdfgfdg'
WHERE BookID = '2';

Library System: Notes and References

One Drive URL for the Library System:

http://1drv.ms/1dSRFWE

CPU Notes and References:

http://1drv.ms/1dSRLxt

CCS 2600 Software Development Title Defense Structure

The following will be the content of the presentation during the Title Defense of your proposed System Development in CCS 2600: Programming with Databases:

I. Title
II. Introduction
 - This section will discuss the current state of technology that the clients' currently employ, whether it is done manually or with few technologies. Usually in paragraph form, and short and concise.
III. Statement of the Problems
 - This section will identify the problems encountered by the client using the current state of teachnology. (In bullets)
III. General Objective
 - The overall goal of the development process. (Ex. To create the Inventory System for Jing Jing's Sari-sari Store)
IV. Specific Objectives
 - This elaborates the general objectives and states the specific steps to accomplish it. These must be specific, measurable, attainable, realistic. Usually divided by the scope and thus defines the main modules in the system. (Ex. In an inventory system, the modules will be adding products, stock in and point of sale. Thus, one specific objective will be To create a Point of Sale module that manage the sales of the system.) Avoid the technicalities such as To pass the subject CCS 2600. (Numbered, ordered by process what comes first and must answer each statement of the problem)
V. Significance of the Study
- This section explains why research must be done in this area and what are the benefits.
VI. Members and Roles. (Team Leader first then others alphabetical)

Good luck!

(Source: Modified Capstone Format)

Chapter 3 and 4 of Fundamentals of Database Design

The following are the links for the presentations on The Fundamentals of Database Design:

Chapter 3 - Relational Model

Chapter 4 - ERD

Or you could check out my SkyDrive for additional references.

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