Database-Design
如何為典型的大學框架創建架構？
我有兩張桌子：
部門
PK：部門編號
院系
PK：FacultyId
FK：DeptId
我想要的是在
Departments名為的表中添加另一列，該列HOD將保存作為部門 HOD 的教師的 ID。我在想我必須FacultyId在表中創建一個 ForeignKeyDeparments。但是，這會產生循環依賴。在這種情況下，我該如何創建我的表格？
考慮到 Faculty 表確實儲存了人，所以我們稱之為Staff。那麼是的，在部門和員工之間建立一對多的關係是有意義的，因此從員工到部門的外鍵來表達一個人可以管理多個部門的事實。
問題是您不能使用預定義的外鍵創建兩個表：這顯然會失敗，因為每個表都引用另一個表。因此，正確的步驟是：
- 創建表
- 填充它們
- 添加外鍵約束。
這是一個使用簡單 HR 模式
EMPLOYEES和的範例DEPARTMENTS。所有員工都在一個部門工作，有些員工還管理一個或多個部門。這是使用 Oracle 語法，但您可以輕鬆地轉置到任何關係數據庫。請注意，員工必須屬於某個部門。但是一個部門可以在沒有任何經理的情況下存在。要強制部門始終擁有經理，您需要向列添加
NOT NULL約束MANAGER_ID。還要注意，一個經理可以管理多個部門（即使樣本數據沒有這種情況）。
創建表：
CREATE TABLE departments ( department_id NUMBER primary key, department_name VARCHAR2(30), manager_id NUMBER ); CREATE TABLE employees ( employee_id NUMBER primary key, first_name VARCHAR2(20), last_name VARCHAR2(25), email VARCHAR2(25), phone_number VARCHAR2(20), department_id NUMBER NOT NULL );填充部門：
INSERT INTO departments VALUES (10,'Administration',200); INSERT INTO departments VALUES (20,'Marketing',201); INSERT INTO departments VALUES (30,'Purchasing',114); INSERT INTO departments VALUES (40,'Human Resources',203); INSERT INTO departments VALUES (50,'Shipping',121); INSERT INTO departments VALUES (60 ,'IT',103); INSERT INTO departments VALUES (70 ,'Public Relations',204); INSERT INTO departments VALUES (80 ,'Sales',145); INSERT INTO departments VALUES (90 ,'Executive',100); INSERT INTO departments VALUES (100 ,'Finance',108); INSERT INTO departments VALUES (110 ,'Accounting',205); INSERT INTO departments VALUES (120 ,'Treasury',NULL); INSERT INTO departments VALUES (130 ,'Corporate Tax',NULL); INSERT INTO departments VALUES (140 ,'Control And Credit',NULL); INSERT INTO departments VALUES (150 ,'Shareholder Services',NULL); INSERT INTO departments VALUES (160 ,'Benefits',NULL); INSERT INTO departments VALUES (170 ,'Manufacturing',NULL); INSERT INTO departments VALUES (180 ,'Construction',NULL); INSERT INTO departments VALUES (190 ,'Contracting',NULL); INSERT INTO departments VALUES (200 ,'Operations',NULL); INSERT INTO departments VALUES (210 ,'IT Support',NULL); INSERT INTO departments VALUES (220 ,'NOC',NULL); INSERT INTO departments VALUES (230 ,'IT Helpdesk',NULL); INSERT INTO departments VALUES (240 ,'Government Sales',NULL); INSERT INTO departments VALUES (250 ,'Retail Sales',NULL); INSERT INTO departments VALUES (260 ,'Recruiting',NULL); INSERT INTO departments VALUES (270 ,'Payroll',NULL); COMMIT;填充員工：
INSERT INTO employees VALUES (100,'Steven','King','SKING','515.123.4567',90); INSERT INTO employees VALUES (101,'Neena','Kochhar','NKOCHHAR','515.123.4568',90); INSERT INTO employees VALUES (102,'Lex','De Haan','LDEHAAN','515.123.4569',90); INSERT INTO employees VALUES (103,'Alexander','Hunold','AHUNOLD','590.423.4567',60); INSERT INTO employees VALUES (104,'Bruce','Ernst','BERNST','590.423.4568',60); INSERT INTO employees VALUES (105,'David','Austin','DAUSTIN','590.423.4569',60); INSERT INTO employees VALUES (106,'Valli','Pataballa','VPATABAL','590.423.4560',60); INSERT INTO employees VALUES (107,'Diana','Lorentz','DLORENTZ','590.423.5567',60); INSERT INTO employees VALUES (108,'Nancy','Greenberg','NGREENBE','515.124.4569',100); INSERT INTO employees VALUES (109,'Daniel','Faviet','DFAVIET','515.124.4169',100); INSERT INTO employees VALUES (110,'John','Chen','JCHEN','515.124.4269',100); INSERT INTO employees VALUES (111,'Ismael','Sciarra','ISCIARRA','515.124.4369',100); INSERT INTO employees VALUES (112,'Jose Manuel','Urman','JMURMAN','515.124.4469',100); INSERT INTO employees VALUES (113,'Luis','Popp','LPOPP','515.124.4567',100); INSERT INTO employees VALUES (114,'Den','Raphaely','DRAPHEAL','515.127.4561',30); INSERT INTO employees VALUES (115,'Alexander','Khoo','AKHOO','515.127.4562',30); INSERT INTO employees VALUES (116,'Shelli','Baida','SBAIDA','515.127.4563',30); INSERT INTO employees VALUES (117,'Sigal','Tobias','STOBIAS','515.127.4564',30); INSERT INTO employees VALUES (118,'Guy','Himuro','GHIMURO','515.127.4565',30); INSERT INTO employees VALUES (119,'Karen','Colmenares','KCOLMENA','515.127.4566',30); INSERT INTO employees VALUES (120,'Matthew','Weiss','MWEISS','650.123.1234',50); INSERT INTO employees VALUES (121,'Adam','Fripp','AFRIPP','650.123.2234',50); INSERT INTO employees VALUES (122,'Payam','Kaufling','PKAUFLIN','650.123.3234',50); INSERT INTO employees VALUES (123,'Shanta','Vollman','SVOLLMAN','650.123.4234',50); INSERT INTO employees VALUES (124,'Kevin','Mourgos','KMOURGOS','650.123.5234',50); INSERT INTO employees VALUES (125,'Julia','Nayer','JNAYER','650.124.1214',50); INSERT INTO employees VALUES (126,'Irene','Mikkilineni','IMIKKILI','650.124.1224',50); INSERT INTO employees VALUES (127,'James','Landry','JLANDRY','650.124.1334',50); INSERT INTO employees VALUES (128,'Steven','Markle','SMARKLE','650.124.1434',50); INSERT INTO employees VALUES (129,'Laura','Bissot','LBISSOT','650.124.5234',50); INSERT INTO employees VALUES (130,'Mozhe','Atkinson','MATKINSO','650.124.6234',50); INSERT INTO employees VALUES (131,'James','Marlow','JAMRLOW','650.124.7234',50); INSERT INTO employees VALUES (132,'TJ','Olson','TJOLSON','650.124.8234',50); INSERT INTO employees VALUES (133,'Jason','Mallin','JMALLIN','650.127.1934',50); INSERT INTO employees VALUES (134,'Michael','Rogers','MROGERS','650.127.1834',50); INSERT INTO employees VALUES (135,'Ki','Gee','KGEE','650.127.1734',50); INSERT INTO employees VALUES (136,'Hazel','Philtanker','HPHILTAN','650.127.1634',50); INSERT INTO employees VALUES (137,'Renske','Ladwig','RLADWIG','650.121.1234',50); INSERT INTO employees VALUES (138,'Stephen','Stiles','SSTILES','650.121.2034',50); INSERT INTO employees VALUES (139,'John','Seo','JSEO','650.121.2019',50); INSERT INTO employees VALUES (140,'Joshua','Patel','JPATEL','650.121.1834',50); INSERT INTO employees VALUES (141,'Trenna','Rajs','TRAJS','650.121.8009',50); INSERT INTO employees VALUES (142,'Curtis','Davies','CDAVIES','650.121.2994',50); INSERT INTO employees VALUES (143,'Randall','Matos','RMATOS','650.121.2874',50); INSERT INTO employees VALUES (144,'Peter','Vargas','PVARGAS','650.121.2004',50); INSERT INTO employees VALUES (145,'John','Russell','JRUSSEL','011.44.1344.429268',80); INSERT INTO employees VALUES (146,'Karen','Partners','KPARTNER','011.44.1344.467268',80); INSERT INTO employees VALUES (147,'Alberto','Errazuriz','AERRAZUR','011.44.1344.429278',80); INSERT INTO employees VALUES (148,'Gerald','Cambrault','GCAMBRAU','011.44.1344.619268',80); INSERT INTO employees VALUES (149,'Eleni','Zlotkey','EZLOTKEY','011.44.1344.429018',80); INSERT INTO employees VALUES (150,'Peter','Tucker','PTUCKER','011.44.1344.129268',80); INSERT INTO employees VALUES (151,'David','Bernstein','DBERNSTE','011.44.1344.345268',80); INSERT INTO employees VALUES (152,'Peter','Hall','PHALL','011.44.1344.478968',80); INSERT INTO employees VALUES (153,'Christopher','Olsen','COLSEN','011.44.1344.498718',80); INSERT INTO employees VALUES (154,'Nanette','Cambrault','NCAMBRAU','011.44.1344.987668',80); INSERT INTO employees VALUES (155,'Oliver','Tuvault','OTUVAULT','011.44.1344.486508',80); INSERT INTO employees VALUES (156,'Janette','King','JKING','011.44.1345.429268',80); INSERT INTO employees VALUES (157,'Patrick','Sully','PSULLY','011.44.1345.929268',80); INSERT INTO employees VALUES (158,'Allan','McEwen','AMCEWEN','011.44.1345.829268',80); INSERT INTO employees VALUES (159,'Lindsey','Smith','LSMITH','011.44.1345.729268',80); INSERT INTO employees VALUES (160,'Louise','Doran','LDORAN','011.44.1345.629268',80); INSERT INTO employees VALUES (161,'Sarath','Sewall','SSEWALL','011.44.1345.529268',80); INSERT INTO employees VALUES (162,'Clara','Vishney','CVISHNEY','011.44.1346.129268',80); INSERT INTO employees VALUES (163,'Danielle','Greene','DGREENE','011.44.1346.229268',80); INSERT INTO employees VALUES (164,'Mattea','Marvins','MMARVINS','011.44.1346.329268',80); INSERT INTO employees VALUES (165,'David','Lee','DLEE','011.44.1346.529268',80); INSERT INTO employees VALUES (166,'Sundar','Ande','SANDE','011.44.1346.629268',80); INSERT INTO employees VALUES (167,'Amit','Banda','ABANDA','011.44.1346.729268',80); INSERT INTO employees VALUES (168,'Lisa','Ozer','LOZER','011.44.1343.929268',80); INSERT INTO employees VALUES (169,'Harrison','Bloom','HBLOOM','011.44.1343.829268',80); INSERT INTO employees VALUES (170,'Tayler','Fox','TFOX','011.44.1343.729268',80); INSERT INTO employees VALUES (171,'William','Smith','WSMITH','011.44.1343.629268',80); INSERT INTO employees VALUES (172,'Elizabeth','Bates','EBATES','011.44.1343.529268',80); INSERT INTO employees VALUES (173,'Sundita','Kumar','SKUMAR','011.44.1343.329268',80); INSERT INTO employees VALUES (174,'Ellen','Abel','EABEL','011.44.1644.429267',80); INSERT INTO employees VALUES (175,'Alyssa','Hutton','AHUTTON','011.44.1644.429266',80); INSERT INTO employees VALUES (176,'Jonathon','Taylor','JTAYLOR','011.44.1644.429265',80); INSERT INTO employees VALUES (177,'Jack','Livingston','JLIVINGS','011.44.1644.429264',80); INSERT INTO employees VALUES (178,'Kimberely','Grant','KGRANT','011.44.1644.429263',NULL); INSERT INTO employees VALUES (179,'Charles','Johnson','CJOHNSON','011.44.1644.429262',80); INSERT INTO employees VALUES (180,'Winston','Taylor','WTAYLOR','650.507.9876',50); INSERT INTO employees VALUES (181,'Jean','Fleaur','JFLEAUR','650.507.9877',50); INSERT INTO employees VALUES (182,'Martha','Sullivan','MSULLIVA','650.507.9878',50); INSERT INTO employees VALUES (183,'Girard','Geoni','GGEONI','650.507.9879',50); INSERT INTO employees VALUES (184,'Nandita','Sarchand','NSARCHAN','650.509.1876',50); INSERT INTO employees VALUES (185,'Alexis','Bull','ABULL','650.509.2876',50); INSERT INTO employees VALUES (186,'Julia','Dellinger','JDELLING','650.509.3876',50); INSERT INTO employees VALUES (187,'Anthony','Cabrio','ACABRIO','650.509.4876',50); INSERT INTO employees VALUES (188,'Kelly','Chung','KCHUNG','650.505.1876',50); INSERT INTO employees VALUES (189,'Jennifer','Dilly','JDILLY','650.505.2876',50); INSERT INTO employees VALUES (190,'Timothy','Gates','TGATES','650.505.3876',50); INSERT INTO employees VALUES (191,'Randall','Perkins','RPERKINS','650.505.4876',50); INSERT INTO employees VALUES (192,'Sarah','Bell','SBELL','650.501.1876',50); INSERT INTO employees VALUES (193,'Britney','Everett','BEVERETT','650.501.2876',50); INSERT INTO employees VALUES (194,'Samuel','McCain','SMCCAIN','650.501.3876',50); INSERT INTO employees VALUES (195,'Vance','Jones','VJONES','650.501.4876',50); INSERT INTO employees VALUES (196,'Alana','Walsh','AWALSH','650.507.9811',50); INSERT INTO employees VALUES (197,'Kevin','Feeney','KFEENEY','650.507.9822',50); INSERT INTO employees VALUES (198,'Donald','OConnell','DOCONNEL','650.507.9833',50); INSERT INTO employees VALUES (199,'Douglas','Grant','DGRANT','650.507.9844',50); INSERT INTO employees VALUES (200,'Jennifer','Whalen','JWHALEN','515.123.4444',10); INSERT INTO employees VALUES (201,'Michael','Hartstein','MHARTSTE','515.123.5555',20); INSERT INTO employees VALUES (202,'Pat','Fay','PFAY','603.123.6666',20); INSERT INTO employees VALUES (203,'Susan','Mavris','SMAVRIS','515.123.7777',40); INSERT INTO employees VALUES (204,'Hermann','Baer','HBAER','515.123.8888',70); INSERT INTO employees VALUES (205,'Shelley','Higgins','SHIGGINS','515.123.8080',110); INSERT INTO employees VALUES (206,'William','Gietz','WGIETZ','515.123.8181',110); COMMIT;添加外鍵約束：
ALTER TABLE employees ADD CONSTRAINT fk_employees_departments department_id REFERENCES departments; ALTER TABLE departments ADD CONSTRAINT fk_departments_employees manager_id REFERENCES employees;