database_creation.sql //รีบทำให้เสร็จ - TopicsExpress

database_creation.sql //รีบทำให้เสร็จ มีงานอื่นต้องทำอีกเยอะ!!! พยายามกันหน่อย สู้ๆ มีใจจดจ่ออยู่กับงาน เพื่อจะได้เรียนจบกันทุกคนนะครับ ^_^ code นี้นำไปใช้สร้างตารางได้เลย ส่วนการลงข้อมูล ต้องลงเป็นลำดับก่อน-หลัง ตามนี้ 1. Brand 2. Type 3. Catalog 4. StockB 5. Branch * 6. StockA 7. Serial 8. Telephone 9. PointOfSale 10. Staff * 11. Sale 12. Cancellation 13. List * อาจมีปัญหาในการลงข้อมูลเพราะ Foreign key ก่อนส่งมาให้ดูให้ลองเทสก่อน และตรวจสอบความถูกต้องด้วย ห้ามพิมพ์ผิด พิมพ์ตก เจอบ่อยมาก และก็เรียง code เว้นวรรคให้สวยงาม ให้อ่านง่าย เหมือนที่เราทำด้วย file database_creation.sql อยู่ใน dropbox สำหรับแผนภาพที่ให้วาดนั้น มีแก้ตรงชื่อตาราง SerialNo ให้เปลี่ยนเป็น Serial แค่นั้น เนื่องจากชื่อซ้ำกับ Attribute เนื่องจากใน Database มันเป็น case insensitive /* Database Creation */ /* 1 */ create table brand ( brand_id char(3) constraint brand_id_pk primary key, brand_name varchar2(20) constraint brand_name_nn not null constraint brand_name_uk unique ); /* 2 */ create table type ( type_id char(2) constraint type_id_pk primary key, tpye_name varchar2(20) constraint type_name_nn not null constraint type_name_uk unique ); /* 3 */ create table catalog ( part_no varchar2(20) constraint cat_part_pk primary key, detail varchar2(100) constraint cat_detail_nn not null, price number(6, 2) constraint cat_price_nn not null, brand_id char(3) constraint cat_brand_nn not null constraint cat_brand_fk references brand(brand_id), type_id char(2) constraint cat_type_nn not null constraint cat_type_fk references type(type_id) ); /* 4 */ create table stock_b ( barcode char(9) constraint stock_b_code_pk primary key, part_no varchar2(20) constraint stock_b_part_nn not null constraint stock_b_part_uk unique constraint stock_b_part_fk references catalog(part_no) ); /* 5 */ create table branch ( branch_id char(3) constraint branch_id_pk primary key, branch_name varchar2(30) constraint branch_name_nn not null, house_no varchar2(10) constraint branch_house_nn not null, village_no number(2), lane varchar2(30), road varchar2(30), sub_district varchar2(20) constraint branch_sub_nn not null, district varchar2(20) constraint branch_district_nn not null, province varchar2(20) constraint branch_province_nn not null, post_code char(5) constraint branch_post_nn not null, email varchar2(30) constraint branch_email_nn not null constraint branch_email_uk unique, manager_id char(7) constraint branch_manager_nn not null constraint branch_manager_uk unique /* branch_manager_fk */ ); /* 6 */ create table stock_a ( branch_id char(3) constraint stock_a_branch_fk references branch(branch_id), barcode char(9) constraint stock_a_code_fk references stock_b(barcode), quantity number(4) default 0 constraint stock_a_qty_nn not null constraint stock_a_qty_ck check(quantity >= 0), date_added timestamp default sysdate constraint stock_a_added_nn not null, last_sold timestamp, status char(1), constraint stock_a_branch_code_pk primary key(branch_id, barcode), constraint stock_a_sold_ck check(last_sold >= date_added) ); /* 7 */ create table serial ( serial_no varchar2(30) constraint serial_serial_pk primary key, branch_id char(3) constraint serial_branch_nn not null, barcode char(9) constraint serial_code_nn not null, constraint serial_serial_code_fk foreign key(branch_id, barcode) references stock_a(branch_id, barcode) ); /* 8 */ create table telephone ( tel_no varchar2(10) constraint tel_no_pk primary key, branch_id char(3) constraint tel_branch_nn not null constraint tel_branch_fk references branch(branch_id) ); /* 9 */ create table point_of_sale ( pos_id char(5) constraint pos_id_pk primary key, branch_id char(3) constraint pos_branch_nn not null constraint pos_branch_fk references branch(branch_id) ); /* 10 */ create table staff ( staff_id char(7) constraint staff_id_pk primary key, first_name varchar2(30) constraint staff_fname_nn not null, last_name varchar2(30) constraint staff_lname_nn not null, start_date date default sysdate constraint staff_date_nn not null, end_date date, house_no varchar2(10) constraint staff_house_nn not null, village_no number(2), lane varchar2(30), road varchar2(30), sub_district varchar2(20) constraint staff_sub_nn not null, district varchar2(20) constraint staff_district_nn not null, province varchar2(20) constraint staff_province_nn not null, post_code char(5) constraint staff_post_nn not null, tel_no varchar2(10) constraint staff_tel_nn not null constraint staff_tel_uk unique, email varchar2(30) constraint staff_email_nn not null constraint staff_email_uk unique, branch_id char(3) constraint staff_branch_nn not null constraint staff_branch_fk references branch(branch_id), leader_id char(7) constraint staff_leader_nn not null constraint staff_leader_fk references staff(staff_id), constraint staff_end_ck check(end_date > start_date) ); /* branch_manager_fk for table 5 */ alter table branch add constraint branch_manager_fk foreign key (manager_id) references staff(staff_id); /* 11 */ create table sale ( pos_id char(5) constraint sale_pos_fk references point_of_sale(pos_id), trans_id char(5), date_of_sale timestamp, staff_id char(7) constraint sale_staff_nn not null constraint sale_staff_fk references staff(staff_id), constraint sale_pos_trans_date_pk primary key(pos_id, trans_id, date_of_sale) ); /* 12 */ create table cancellation ( pos_id char(5), trans_id char(5), date_of_sale timestamp, date_of_cancellation timestamp constraint cancel_cancel_nn not null, staff_id char(7) constraint cancel_staff_nn not null, constraint cancel_cancel_ck check(date_of_cancellation > date_of_sale), constraint cancel_pos_trans_sale_pk primary key(pos_id, trans_id, date_of_sale), constraint calcel_pos_trans_sale_fk foreign key(pos_id, trans_id, date_of_sale) references sale(pos_id, trans_id, date_of_sale) ); /* 13 */ create table list ( pos_id char(5), trans_id char(5), date_of_sale timestamp, no number(3), branch_id char(3) constraint list_branch_nn not null, barcode char(9) constraint list_code_nn not null, serial_no varchar2(30) constraint list_serial_uk unique, constraint list_pos_trans_no_pk primary key(pos_id, trans_id, date_of_sale, no), constraint list_pos_trans_sale_fk foreign key(pos_id, trans_id, date_of_sale) references sale(pos_id, trans_id, date_of_sale), constraint list_branch_code_fk foreign key(branch_id, barcode) references stock_a(branch_id, barcode) ); Numnim Jungka Tor Bowornthat

Dr Steve Parsons ~ Is Looking To HELP People Who Need INCOME...Are

The greatest speech of humanity...............Tal. We want to

Acer Aspire NX.MFQAA.005;V5-123-3466 11.6-Inch Laptop :: Cyber

Heavenly Father, thank you for this wonderful feeling to wake up

Palavras para quê?! Tá tudo dito... As premonições de

Throughout our lives we meet many, many people. Some will pass

Highly sensitive people are too often perceived as weaklings or

Pengertian Jutsu Dalam Naruto Posted By adm Kingdom (Like Dulu

que tipos mas caraduras y mediocres loco - como van a afanar los

went to the movies solo again to see lee daniels the butler.. i

Questions asked in SBI Associates Exam ( 24- jan-2015, Morning

The current era has certainly changed from the Cold War in the

CBSE examination Board has decided to introduce the provision of

Nejdříve si položíme otázku. Čeho chceme v životě

PANS LABYRINTH 2007 Original Japanese Movie Poster Roger

15$ (700 PHP) only / Quality / DDoS protected / Free

Mariah Carey’s version in the 90s brought Without You” back

Turn a measly $1.75 into $100,000+++. This $1.75 Investment.

Well a day in my life to give thanks to MY JESUS . Today i am

Three days before the 2010 election, Cutler gathered supporters in

HOT MAMA SALSA FANS: Last week, Jess Horton, The Fit Petite,