Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have two tables:
PK: DeptId
| DeptId | DeptName |
|---|
PK: FacultyId
FK: DeptId
| FacultyId | FacultyName | DeptId |
|---|
What I want is to add another column in the Departments table called HOD which will hold the Id of the Faculty who's the HOD of the Department. I was thinking that I'll have to make the FacultyId a ForeignKey in the Deparments table. However, that'll create a cyclic dependency.
How do I go about creating my Tables in this situation?
Considering that the faculty table really stores people, let's call is staff. Then yes, it makes sense to have a one-to-many relationship between departments and staff, and therefore a foreign key from staff to departments to express the fact an individual can manage several departments.
The problem is that you cannot create both tables with their foreign keys pre-defined: that will obviously fail since each refers to the other. The proper steps are therefore:
Here is an example that uses a simple HR schema with EMPLOYEES and DEPARTMENTS. All employees work in a department, and some employees also manage one or more departments. This is using Oracle syntax, but you can easily transpose to any relational database.
Note that an employee must belong to a department. But an department can exist without any manager. To make it mandatory for a department to always have a manager, you need to add a NOT NULL constraint to the MANAGER_ID column.
Note also that a manager could manage multiple departments (even though the sample data does not have such a case).
Create the tables:
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
);
Populate departments:
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;
Populate employees:
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;
Add foreign key constraints:
ALTER TABLE employees ADD CONSTRAINT fk_employees_departments department_id REFERENCES departments;
ALTER TABLE departments ADD CONSTRAINT fk_departments_employees manager_id REFERENCES employees;
NOT NULL constraint, I will have to add it later while creating the foreign keys, right?
Nov 3, 2021 at 9:50
manager_id number not null. That means those departments without any manager - like INSERT INTO departments VALUES (220 ,'NOC',NULL); will fail.
Nov 3, 2021 at 12:56
manager_id is not nullable, what value of Employee ID will I insert? Because, as employee_dept_id in also not nullable, I'll need a dept_id, which will need the manager_id, which will require employee_id and so on... (Sorry for asking repeatedly, but I'm kind of confused)
Nov 3, 2021 at 13:24
and hence must be stored in Faculties as boolean column- this is incorrect AFAICS - the OP says:which will hold the Id of the Faculty who's the HOD of the Department.which makes sense to me! AFAIK, normally university departements have their own heads - and that person may or may not also be the faculty head? I'd have a faculty head (ID - FK into a staff table) field for the faculty table and the same for departement head!