PART A DATABASE DESIGN AND IMPLEMENTATION (40 points)
You are going to design and implement a database for a website that provides
information about different designers, design companies, and design products.
The data included for the designers, companies and products are described
below.
For each company, the website will collect the following data,
- Company logo (optional)
- Full company name
- Short description
- Address (optional)
- Phone number (optional)
- Fax number (optional)
- Country
- Web site URL of the company
- Field(s) Select max. 5 fields from the list provided.
For each designer, the website will collect the following data,
- Recent portrait photo(s)
- Full name
- Birthdate (year)
- Short description
- Address (optional)
- Phone number (optional)
- Fax number (optional)
- Country
- Web site URL of the designer
- Company(optional)
- Field(s) Select max. 5 fields from the list provided.
For each product, the website will collect the following data,
- Related images
- Product name
- Year of design
- Short description
- Web site URL of the product (optional)
- Field(s) Select max. 5 fields from the list provided.
- Designer(s), Select designer(s) related to this product from the existed list of
designers
- Company (optional), Select the company related to this product from the
existing list.
- Material(s) (optional), Select max. 5 materials from the list provided.
PART B PL/SQL PROGRAMMING (45 points)
1. A PL/SQL anonymous block (5 points)
The business hours for office of the company that run the website are 8am
to 9pm, Sunday through Friday, and 8am to noon on Saturday. Write a
PL/SQL block to check if the current time is within the business hours. If
current time is within the business hours, the output should be Office
Open; else, the output should be Office Closed.
Deliverable: Save the PL/SQL script of the anonymous block in a file called
PARTB1.sql.
2. Create a stored package called DESIGNDB_PKG which includes,
A stored function (10 points)
Create a stored function called GET_PRODNUM to count the number of
products related to a specific designer. The function should accept a
designer ID as the input parameter and return a number, which indicates
the number of products related to the designer. Add exception handling for
invalid input.
A stored procedure with cursor (15 points)
Create stored procedure called GET_PRODLIST. The stored procedure
should accept a company ID as the input. The procedure will print on the
screen the name and the country of the company. Also, it will print on the
screen the name and birth date(year) for each designer belong to the
company, as well as all the name and year of design for each product
related to the company.
The lists of designers are sorted according to the name of the designer in
ascending order. The lists of products are sorted according to the year of
design (descending) and product name (ascending). Add exception
handling for invalid input.
For example, if you user input a company ID, 5 (which is a valid company
ID, for the company named ICM). The output will look like:
ICM has the following designers and products,
Designers:
Athanassios Babalis (1998)
Banu Alpay (1976)
Karim Rashid (1973)
Ozlem Yalim (1945)
Ozay Emert (1986)
Products:
Mother of Pearl (2007)
Omega (2006)
Wingring (2006)
Jap-puccino (2005)
A stored package (5 points)
Create the package specification and package body for the package
called DESIGNDB_PKG including the stored function GET_PRODNUM
and stored procedure GET_PRODLIST.
Deliverable: Save the PL/SQL script for creating the package in a file called
PARTB2.sql.
3. Trigger (10points)
Create a trigger called CHK_BIRTHYR. Before the user insert/update the
year of birth of one or a list of designers, this trigger will fire and check if
the new values of the designers!ˉ ages are over 15. The age of a designer
is calculated by the current year ¨C year of birth of the designer. If the
designer!ˉs age is under 15, use the RAISE_APPICATION_ERROR
function to output an error code and error message.
Deliverable: Save the PL/SQL script for creating and testing the trigger in a file
called PARTB3.sql.
解决方案 »
- 行号=1列号41 出现符号"@"在需要下列之一时: an identifier a double-quoted delimited-identifier
- oracle9i 误删除表
- sql语句优化
- connect by、rownum、level、lead、lag很好很强大(散分)
- 关于Oracle导数据的问题---高分(100)
- 菜鸟问题:关于rowid和rownum
- 急!关于long row字段问题?
- 求教:SQL语句怎么写??
- oracle 11g登陆后提示“出现内部错误。有关详细信息, 请查看日志文件。"怎么解决?
- windows server 2008 R2系统下Oracle VM VirtualBox不能为虚拟电脑打开一个新任务?
- 如何解决ora-01008 并非所有变量都已关联的错误
- 查询最小值的弱问题
不过题应该是不难的.