最近发现园子里面关于数据库方面的文章比较多，正好我也是一个喜欢凑热闹的家伙，那就跟着烧一把火吧。^_^
这是前阵子一个朋友面试外企的一套关于数据库设计的试题，有兴趣的朋友不妨一试。
Part I
工厂在定义一个新产品的流程如下：
1. 确定产品信息如名字，代号等；
2. 设计部门设计出产品的结构；
3. 在确定结构后就可以得出每个产品所需要的基本零件及其所需的数据。进而可以进行相关财务处理与采购处理。
其相关逻辑如下：
. 对于每一种基本零件都有其相关的型号等资料设定；
. 对于每个供应商也有其相关资料的设定；
. 一个基本零件可以由不同的供应商供应，其价格等资料会有些差异；
. 一个供应商也可以供应多种不同的基本零件；
. 对于一种产品，可以由多个大部件构成，不同的产品有可能由相同的大部件组成；
例如，某发动机可以是汽车A的一个大部件，也可以是汽车B的一个大部件。
. 在部件结构中，某些部件是由别外一个或多个部件组成的，而所有的部件最终都是由基本零件构成的；
. 产品大部件只是存放与产品直接相关的最上层部件。
Product(产品) | Supplier(供应商) | Part(基本零件) | |||
product_code | supplier_code | part_code | |||
product_name | supplier_name | part_name | |||
product_desc | address_1 | part_desc | |||
created_date | address_2 | remark | |||
address_3 | |||||
Product_comp(产品大部件) | Component(部件结构) | component_supply(零件供应) | |||
product_code | comp_id | supply_id | |||
comp_id | component_code | part_code | |||
number_component | component_name | supplier_code | |||
parent_comp_id | price | ||||
supply_id | ind_active | ||||
number_component | |||||
Prod_part_supp_detail(产品零件明细) | |||||
product_code | |||||
supply_id | |||||
number_component | |||||
Below is a structure example of product 'P_1' ：
1. 根据理解画出E-R(或UML)联系图；
2. 写脚本来建立所需要的数据库对象，如表、序列、约束等；
3. 为上图所示的产品P_1准备初始化数据（prod_part_supp_detail 除外）；
4. 编写procedure 或function生成指定产品的产品零件明细清单；
5. Write a SQL to show the supplier and component detail of product. For amount field, use below logic :
If the “Amount” < 1000 show “Low”
if “Amount” >=1000<10000 show “Middle”
else show “High”
The layout is :
Supplier Name Part Name Necessary Num Amount Type
------------------ ------------------ -------------- ---------------- ----------
XXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXX 9999999999 999999999.99 XXXXXXXXXX
如果需要用到递归，以下是一个简单示例:
create or replace function get_char_one_by_one(v_str varchar2)
return varchar2
is
begin
if v_str is not null and length(v_str) > 1 then
return substr(v_str, 1, 1) || ' ' ||
get_char_one_by_one(substr(v_str, 2));
else
return v_str;
end if;
end;
/
set serveroutput on
begin
dbms_output.put_line(get_char_one_by_one('I am good boy'));
end;
/
在以下两题中作选一题
Part II
There are some tables need copy from one oracle user User1 to another user User2.
1. The DB structure of source user is compatiable to the target db user (All field in source db user are exists in target db user, but maybe some fields in target db user not in source db user);
2. No DB Link or privilege granted between those two users;
3. No primary key or unique key conflict between those two users' data;
4. The records number is not very much (at most 10000 for one table).
Part III
There are some tables need copy from one oracle user User1 to another user User2.
1. The DB structure of source user is compatiable to the target db user (All field in source db user are exists in target db user, but maybe some fields in target db user not in source db user);
2. Can create DB Link or grant priv between those two users;
3. No primary key or unique key conflict between those two users' data;
4. The records number is not very much (at most 10000 for one table).
