· 5 years ago · Apr 26, 2020, 12:48 PM
1CREATE DATABASE IF NOT EXISTS `university`;
2USE `university`;
3CREATE TABLE IF NOT EXISTS `department` (
4 `d_code` enum('CEN','BINF','BAF','ECE','BUS','ECO','ARCH') NOT NULL,
5 `dname` varchar(50) NOT NULL,
6 `dmanager` char(8) NOT NULL,
7 `dfaculty` varchar(50) NOT NULL,
8 `dlocation` enum('Building A','Building E') DEFAULT NULL,
9 PRIMARY KEY (`d_code`)
10) ENGINE=InnoDB DEFAULT CHARSET=latin1;
11CREATE TABLE IF NOT EXISTS `person` (
12 `p_id` char(8) NOT NULL,
13 `name` varchar(20) NOT NULL,
14 `surname` varchar(25) DEFAULT NULL,
15 `email` varchar(25) NOT NULL,
16 `pass` varchar(40) NOT NULL,
17 `bdate` char(40) DEFAULT NULL,
18 `dcode` enum('CEN','BINF','BAF','ECE','BUS','ECO','ARCH') NOT NULL,
19 `fathersName` varchar(20) DEFAULT NULL,
20 `pnumber` varchar(20) DEFAULT NULL,
21 `position` enum('Lecturer','Bachelor','Master','Ph.D') DEFAULT NULL,
22 `status` enum('Active','Graduated','Exchange','Onleave','On Suspension') DEFAULT NULL,
23 PRIMARY KEY (`p_id`),
24 CONSTRAINT `department` FOREIGN KEY (`dcode`) REFERENCES `department` (`d_code`) ON DELETE NO ACTION ON UPDATE CASCADE
25) ENGINE=InnoDB DEFAULT CHARSET=latin1;
26CREATE TABLE IF NOT EXISTS `course` (
27 `ccode` char(6) NOT NULL DEFAULT '',
28 `ctitle` varchar(50) DEFAULT NULL,
29 `cdept` enum('CEN','BINF','BAF','ECE','BUS','ECO','ARCH') NOT NULL,
30 `clevel` enum('Bachelor','Master','Ph.D') DEFAULT NULL,
31 `cinfo` tinytext,
32 PRIMARY KEY (`ccode`),
33 FOREIGN KEY (`cdept`) REFERENCES `department` (`d_code`) ON UPDATE CASCADE
34) ENGINE=InnoDB DEFAULT CHARSET=latin1;
35CREATE TABLE IF NOT EXISTS `semester` (
36 `semesterid` char(3) NOT NULL,
37 `syear` year(4) DEFAULT NULL,
38 `sterm` enum('Fall','Spring','Summer') DEFAULT NULL,
39 `sdate` date DEFAULT NULL,
40 `send` date DEFAULT NULL,
41 `duration` tinyint(4) DEFAULT NULL,
42 PRIMARY KEY (`semesterid`)
43) ENGINE=InnoDB DEFAULT CHARSET=latin1;
44CREATE TABLE IF NOT EXISTS `course_open` (
45 `ccopen` char(6) NOT NULL,
46 `p_id` char(8) NOT NULL,
47 `semesterid` char(3) NOT NULL,
48 `ccode` char(6) NOT NULL DEFAULT '',
49 `chours` tinyint(4) DEFAULT NULL,
50 `ects` tinyint(4) DEFAULT NULL,
51 PRIMARY KEY (`ccopen`),
52 KEY `course_idx` (`ccode`),
53 KEY `course_lecturer` (`p_id`),
54 KEY `semester` (`semesterid`),
55 CONSTRAINT `course` FOREIGN KEY (`ccode`) REFERENCES `course` (`ccode`) ON DELETE NO ACTION ON UPDATE CASCADE,
56 CONSTRAINT `course_lecturer` FOREIGN KEY (`p_id`) REFERENCES `person` (`p_id`) ON UPDATE CASCADE,
57 CONSTRAINT `semester` FOREIGN KEY (`semesterid`) REFERENCES `semester` (`semesterid`) ON UPDATE CASCADE
58) ENGINE=InnoDB DEFAULT CHARSET=latin1;
59
60CREATE TABLE IF NOT EXISTS `grades` (
61 `rcode` int(16) NOT NULL AUTO_INCREMENT,
62 `p_id` char(8) NOT NULL,
63 `ccopen` char(6) NOT NULL,
64 `gtype` enum('Midterm','Final','Quiz') DEFAULT NULL,
65 `grade` int(11) DEFAULT NULL,
66 `gweight` int(11) DEFAULT NULL,
67 PRIMARY KEY (`rcode`),
68 KEY `studenttakes` (`p_id`),
69 KEY `coursetaken` (`ccopen`)
70) ENGINE=InnoDB DEFAULT CHARSET=latin1;
71/* Inserting Data */
72INSERT INTO `department` (`d_code`, `dname`, `dmanager`, `dfaculty`, `dlocation`) VALUES
73 ('CEN', 'Computer Engineering', '00002165', 'Faculty of Architecture and Engineering', 'Building E'),
74 ('BINF', 'Business Informatics', '00004011', 'Faculty of Economics and Administrative Sciences', 'Building E'),
75 ('BAF', 'Banking and Finance', '00005008', 'Faculty of Economics and Administrative Sciences', 'Building E'),
76 ('BUS', 'Business Administration', '00003261', 'Faculty of Economics and Administrative Sciences', 'Building E'),
77 ('ECO', 'Economics', '00006277', 'Faculty of Economics and Administrative Sciences', 'Building E'),
78 ('ARCH', 'Architecture', '00001158', 'Faculty of Architecture and Engineering', 'Building A');
79INSERT INTO `course` (`ccode`, `ctitle`, `cdept`, `clevel`, `cinfo`) VALUES
80 ('BUS102', 'MATH. FOR ECONOMICS AND BUSINESS II', 'BUS', 'Bachelor', 'The aim of this course is to give the basic ingredients of mathematics for business and economics. N'),
81 ('BUS103', 'INTRODUCTION TO BUSINESS', 'BUS', 'Bachelor', 'Upon completion of this course you will demonstrate the ability to describe and explain: Basic busin'),
82 ('BUS221', 'MANAGEMENT AND ORGANIZATION', 'BUS', 'Bachelor', 'Basic principles of management are explained. Main functions of management are discussed and some ma'),
83 ('BUS303', 'FUNDAMENTALS OF CORPORATE FINANCE', 'BUS', 'Bachelor', 'The primary objective of this course is to familiarize the student with basic concepts of corporate '),
84 ('CEN103', 'INTRODUCTION TO COMPUTER ENGINEERING', 'CEN', 'Bachelor', 'This course is a general introduction to Computer Science and Engineering. It introduces basic termi'),
85 ('CEN111', 'INTRODUCTION TO ALGORITHMS & PROGRAMMING', 'CEN', 'Bachelor', 'The objective of this course is to equip the students with the basic understanding of algorithms and'),
86 ('CEN112', 'C AND C++ PROGRAMMING', 'CEN', 'Bachelor', 'This course aims to teach and implement the structured programming concepts in C language, an overvi'),
87 ('CEN213', 'OBJECT ORIENTED PROGRAMMING WITH JAVA', 'CEN', 'Bachelor', 'This course calls on you to demonstrate: (1) knowledge of programming techniques and the Java langua'),
88 ('CEN222', 'WEB TECHNOLOGIES', 'CEN', 'Bachelor', 'Teaching to students how to create and publish a basic web page using HTML and its many tags; to describe limitations of creating interactivity including browser support and differences; understanding JavaScript variables, control structures, functions, a'),
89 ('CEN252', 'DATABASE MANAGEMENT SYSTEMS', 'CEN', 'Bachelor', 'The purpose of this course is to provide an introduction to the design and use of database systems, '),
90 ('CEN254', 'DATA STRUCTURES', 'CEN', 'Bachelor', 'To get accustomed to and be able to use common algorithms used in programming, including: divide-and-conquer, greedy algorithms, dynamic programming and analyze the efficiency of algorithms using recurrences and amortized analysis. It covers external memo'),
91 ('CEN303', 'ANALYSIS OF ALGORITHMS', 'CEN', 'Bachelor', 'To learn Divide and conquer techniques, greedy methods, and dynamic programming, Asymptotic notation'),
92 ('ECO206', 'MACROECONOMICS ', 'ECO', 'Bachelor', 'By analyzing macro economic theories, discussing the validity of macroeconomic topics is the aim of ');
93INSERT INTO `semester` (`semesterid`, `syear`, `sterm`, `sdate`, `send`, `duration`) VALUES
94 ('020', '2013', 'Fall', '2012-09-12', '2013-01-28', 16),
95 ('021', '2013', 'Spring', '2013-02-18', '2013-06-05', 16),
96 ('022', '2014', 'Fall', '2013-09-09', '2014-01-16', 16),
97 ('023', '2014', 'Spring', '2014-02-11', '2014-06-02', 16);
98INSERT INTO `person` (`p_id`, `name`, `surname`, `email`, `pass`, `bdate`, `dcode`, `fathersName`, `pnumber`, `position`, `status`) VALUES
99 ('00001043', 'Sonila ', 'Dedja', 'sdedja@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1972-04-30', 'ARCH', 'Saimir ', '3025148', 'Lecturer', 'Active'),
100 ('00001158', 'Jeton ', 'Duka', 'jduka@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1975-04-22', 'ARCH', 'Bledar ', '4256325', 'Lecturer', 'Active'),
101 ('00001234', 'Silvester', 'Stal', 'silvester@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1985-01-01', 'ARCH', 'Ndricim', '5681472', 'Lecturer', 'Active'),
102 ('00002103', 'Ilir', 'Kasapi', 'ikasapi@hotmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1977-03-10', 'CEN', 'Skender', '8254196', 'Lecturer', 'Active'),
103 ('00002157', 'Lindita', 'Baris', 'lbaris@gmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1981-07-02', 'CEN', 'Valmir', '5263145', 'Lecturer', 'Active'),
104 ('00002165', 'Aldo', 'Hoxha', 'ahoxha@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1975-11-18', 'CEN', 'Martin ', '4156854', 'Lecturer', 'Active'),
105 ('00002335', 'Gjon', 'Dibra', 'gdibra@gmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1975-05-01', 'CEN', 'Aleksander', '8526314', 'Lecturer', 'Active'),
106 ('00003261', 'Lorena', 'Domi', 'ldomi@gmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '2015-00-00', 'BUS', 'Saimir ', '3025148', 'Bachelor', 'Active'),
107 ('00004011', 'Igli', 'Maci', 'imaci@epoka.edu.a', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1985-02-22', 'BINF', 'Besim ', '4752369', 'Lecturer', 'Active'),
108 ('00005008', 'Valmir ', 'Rama', 'vrama@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1993-07-22', 'BAF', 'Ergys ', '3215874', 'Lecturer', 'Active'),
109 ('00006277', 'Loren', 'Nano', 'lnano@gmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1970-03-05', 'ECO', 'Artan', '3025148', 'Bachelor', 'Active'),
110 ('01263214', 'Renata ', 'Malaj', 'rmalaj@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1991-10-10', 'ARCH', 'Ergys ', '3215874', 'Bachelor', 'Active'),
111 ('02215698', 'Gledis', 'Basha', 'gbasha@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1994-08-05', 'CEN', 'Elvis ', '4201567', 'Bachelor', 'Active'),
112 ('02245863', 'Klodian', 'Lokaj', 'klokaj@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1989-09-18', 'CEN', 'Bledar ', '4256325', 'Bachelor', 'Active'),
113 ('02345678', 'Miranda', 'Farruku', 'mfarruku@gmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1990-01-01', 'CEN', 'Selim', '3256236', 'Ph.D', 'Active'),
114 ('02358912', 'Ornela ', 'Kamberi', 'okamberi@gmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1992-05-09', 'CEN', 'Erind ', '4225187', 'Bachelor', 'Active'),
115 ('02412630', 'Nensi', 'Xheka', 'nxheka@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1995-05-09', 'CEN', 'Martin ', '4156854', 'Master', 'Exchange'),
116 ('02548924', 'Saimir ', 'Gega', 'sgega@hotmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1993-01-08', 'CEN', 'Rudin', '3009852', 'Bachelor', 'Active'),
117 ('02559855', 'Luela', 'Kabila', 'mdenaj@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1995-08-05', 'CEN', 'Sabri ', '4111025', 'Bachelor', 'Active'),
118 ('02559875', 'Gerta', 'Xhoni', 'gxhoni@hotmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1995-11-11', 'CEN', 'Arif', '4258741', 'Bachelor', 'Graduated'),
119 ('02589584', 'Adela ', 'Cenaj', 'acenaj@gmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1986-03-25', 'CEN', 'Aurel ', '4251963', 'Bachelor', 'Active'),
120 ('02598451', 'Arban', 'Sula', 'rsula@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1991-04-18', 'CEN', 'Mentor ', '3529562', 'Bachelor', 'Active'),
121 ('02653575', 'Gert', 'Rama', 'grama@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1982-11-01', 'CEN', 'Ardit ', '3001000', 'Master', 'Active'),
122 ('02862544', 'Elton ', 'Rudi', 'erudi@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1992-11-07', 'CEN', 'Klevis ', '4225841', 'Bachelor', 'Active'),
123 ('04458795', 'Hysen', 'Dushku', 'hdushku@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1988-03-18', 'BINF', 'Idlir', '3025684', 'Bachelor', 'Active'),
124 ('04559875', 'Marsela', 'Koci', 'mkoci@gmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1985-11-11', 'BINF', 'Agim', '3002244', 'Bachelor', 'Active'),
125 ('04612874', 'Artan', 'Hoxha', 'ahoxha@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1980-04-01', 'BINF', 'Mehdi', '3021874', 'Master', 'Graduated'),
126 ('04659874', 'Xheni', 'Lala', 'xlala@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1992-11-01', 'BINF', 'Besim ', '4752369', 'Bachelor', 'Onleave'),
127 ('04822241', 'Miranda', 'Sokoli', 'ssokoli@hotmail.com', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1990-12-02', 'BINF', 'Dritan ', '3125289', 'Bachelor', 'Graduated'),
128 ('06526384', 'Jona ', 'Veshi', 'jveshi@epoka.edu.al', 'aaf4c61ddcc5e8a2dabede0f3b482cd9aea9434d', '1994-04-02', 'ECO', 'Klajdi ', '4215893', 'Master', 'Active');
129INSERT INTO `course_open` (`ccopen`, `p_id`, `semesterid`, `ccode`, `chours`, `ects`) VALUES
130 ('023001', '00002103', '022', 'CEN222', 4, 4),
131 ('023002', '00002165', '022', 'CEN252', 5, 5),
132 ('023003', '00002335', '022', 'CEN254', 3, 7),
133 ('023004', '00002103', '021', 'CEN111', 3, 5);
134INSERT INTO `grades` (`rcode`, `p_id`, `ccopen`, `gtype`, `grade`, `gweight`) VALUES
135 (10129, '02589584', '023001', 'Quiz', 10, 10),
136 (10130, '02589584', '023002', 'Quiz', 50, 15),
137 (10131, '02589584', '023003', 'Quiz', 100, 10),
138 (10132, '02589584', '023001', 'Midterm', 50, 40),
139 (10133, '02589584', '023002', 'Midterm', 50, 30),
140 (10134, '02589584', '023003', 'Midterm', 90, 45),
141 (10135, '02589584', '023001', 'Final', 15, 50),
142 (10136, '02589584', '023002', 'Final', 80, 55),
143 (10137, '02589584', '023003', 'Final', 75, 45),
144 (10138, '02559855', '023001', 'Quiz', 100, 10),
145 (10139, '02559855', '023002', 'Quiz', 95, 15),
146 (10140, '02559855', '023003', 'Quiz', 85, 10),
147 (10141, '02559855', '023001', 'Midterm', 70, 40),
148 (10142, '02559855', '023002', 'Midterm', 100, 30),
149 (10143, '02559855', '023003', 'Midterm', 50, 45),
150 (10144, '02559855', '023001', 'Final', 85, 50),
151 (10145, '02559855', '023002', 'Final', 55, 55),
152 (10146, '02559855', '023003', 'Final', 75, 45),
153 (10147, '02862544', '023001', 'Quiz', 60, 10),
154 (10148, '02862544', '023002', 'Quiz', 90, 15),
155 (10149, '02862544', '023003', 'Quiz', 60, 10),
156 (10150, '02862544', '023001', 'Midterm', 90, 40),
157 (10151, '02862544', '023002', 'Midterm', 80, 30),
158 (10152, '02862544', '023003', 'Midterm', 75, 45),
159 (10153, '02862544', '023001', 'Final', 80, 50),
160 (10154, '02862544', '023002', 'Final', 60, 55),
161 (10155, '02862544', '023003', 'Final', 75, 45),
162 (10156, '02598451', '023001', 'Quiz', 75, 10),
163 (10157, '02598451', '023002', 'Quiz', 40, 15),
164 (10158, '02598451', '023003', 'Quiz', 15, 10),
165 (10159, '02598451', '023001', 'Midterm', 100, 40),
166 (10160, '02598451', '023002', 'Midterm', 75, 30),
167 (10161, '02598451', '023003', 'Midterm', 80, 45),
168 (10162, '02598451', '023001', 'Final', 90, 50),
169 (10163, '02598451', '023002', 'Final', 70, 55),
170 (10164, '02598451', '023003', 'Final', 50, 45),
171 (10165, '02548924', '023001', 'Quiz', 95, 10),
172 (10166, '02548924', '023002', 'Quiz', 70, 15),
173 (10167, '02548924', '023003', 'Quiz', 60, 10),
174 (10168, '02548924', '023001', 'Midterm', 100, 40),
175 (10169, '02548924', '023002', 'Midterm', 100, 30),
176 (10170, '02548924', '023003', 'Midterm', 95, 45),
177 (10171, '02548924', '023001', 'Final', 100, 50),
178 (10172, '02548924', '023002', 'Final', 80, 55),
179 (10173, '02548924', '023003', 'Final', 90, 45),
180 (10174, '02548924', '023004', 'Final', 95, 50),
181 (10175, '02548924', '023004', 'Midterm', 85, 40),
182 (10176, '02548924', '023004', 'Quiz', 85, 10);
183ALTER TABLE `department` ADD CONSTRAINT `dhead` FOREIGN KEY (`dmanager`) REFERENCES `person` (`p_id`) ON DELETE CASCADE ON UPDATE CASCADE;
184ALTER TABLE `grades` ADD CONSTRAINT `coursetaken` FOREIGN KEY (`ccopen`) REFERENCES `course_open` (`ccopen`) ON UPDATE CASCADE;
185ALTER TABLE `grades` ADD CONSTRAINT `studenttakes` FOREIGN KEY (`p_id`) REFERENCES `person` (`p_id`) ON UPDATE CASCADE;