-
Notifications
You must be signed in to change notification settings - Fork 0
/
emu415.qmd
186 lines (137 loc) · 5.94 KB
/
emu415.qmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
---
styles:
styles.css
format:
html:
title: 'Project'
toc: true
toc-depth: 6
toc-title: Contents
toc-location: left
toc-expand: 5
smooth-scroll: true
anchor-sections: true
include-after-body: abbrv_toc.html
number-sections: true
---
![](sql.gif){style="text-align: center" fig-align="center" width="437"}
## EMU Hacettepe Internship Informing & Feedback System
### Teaser Trailer
{{< video dataciphers.mp4 >}}
### Objective
::: {style="text-align: justify;"}
Our database aims to facilitate the process of selecting and finding companies for internships for students of Industrial Engineering at Hacettepe University. To achieve this, we gather data and evaluations from students who have previously completed their internships at these companies. The information in our database allows students to know what they can expect to experience and what to expect within the opportunities provided by these companies. In addition to evaluations, our database also includes information about the application process for internship programs offered by relevant companies.
:::
### Application Features
::: {style="text-align: justify;"}
- **Company Search:** Users can search for companies offering internships based on various criteria such as location, industry, duration, and specific requirements.
- **Student Evaluation System:** A platform where students can provide feedback and evaluations on their internship experiences, including ratings for different aspects such as work environment, mentor-ship, learning opportunities, and overall satisfaction.
- **Company Profiles:** Detailed profiles for each company, including information about their internship programs, requirements, past interns' experiences, and any additional benefits offered.
- **Analytics and Insights:** Data analytics features to provide insights and trends based on the evaluations and feedback collected from students, helping to identify top-rated companies, popular internship programs, and areas for improvement.
:::
### Relations (Tables) and Their Attributes as an Initial Design
1. **Student**
- student_id (Primary Key)
- first_name
- last_name
- email
- join_year
- gpa
```{r message=FALSE, warning=FALSE}
#| code-fold: true
#| code-summary: "Show the code"
library(RMySQL)
library(readr)
library(dplyr)
library(knitr)
library(lubridate)
# Set up the connection parameters
db_host <- "127.0.0.1" # Localhost address
db_user <- "root" # Your MySQL username
db_password <- "" # Your MySQL password
db_name <- "emu415_ciphers" # The name of your database
# Establish the connection
db_connection <- dbConnect(RMySQL::MySQL(),
host = db_host,
user = db_user,
password = db_password,
dbname = db_name)
# Define the SQL query
sql_query <- "SELECT first_name, last_name, email, join_year FROM students ORDER BY first_name;"
# Execute the SQL query and retrieve the results
results <- dbGetQuery(db_connection, sql_query)
# Display the results
kable(head(results, 10))
```
2. **Company**
- company_id (Primary Key)
- company_name
- industry
- location
- contact_info
- website
```{r message=FALSE, warning=FALSE}
#| code-fold: true
#| code-summary: "Show the code"
# Define the SQL query
sql_query <- "SELECT company_id, company_name, industry, location, contact_info FROM companies;"
# Execute the SQL query and retrieve the results
results <- dbGetQuery(db_connection, sql_query)
# Display the results
kable(head(results, 10))
```
3. **Internship**
- internship_id (Primary Key)
- student_id (Foreign Key)
- company_id (Foreign Key)
- intern_type
- department
- year
- start_date
- end_date
- duration
- income
- working_hours
- transportation_status
```{r message=FALSE, warning=FALSE}
#| code-fold: true
#| code-summary: "Show the code"
# Define the SQL query
sql_query <- "SELECT internship_id, student_id, company_id, intern_type, department, start_date, end_date FROM internships;"
# Execute the SQL query and retrieve the results
results <- dbGetQuery(db_connection, sql_query)
# Display the results
kable(head(results, 10))
```
4. **Rating**
- rating_id (Primary Key)
- internship_id (Foreign Key)
- transportation_rating
- work_env_rating
- learning_opp_rating
- food_rating
- hr_rating
- mentor_rating
- comment
```{r message=FALSE, warning=FALSE}
#| code-fold: true
#| code-summary: "Show the code"
# Define the SQL query
sql_query <- "SELECT rating_id, transportation_rating, work_env_rating, learning_opp_rating, food_rating, hr_rating, mentor_rating FROM ratings;"
# Execute the SQL query and retrieve the results
results <- dbGetQuery(db_connection, sql_query)
# Display the results
kable(head(results, 10))
```
### Relationships
::: {style="text-align: justify;"}
- **internships belongs to company:** One-to-many relationship, each company can offer multiple internships, but each internship is associated with only one company.
- **internship belongs to student**: One-to-many relationship, one student can have multiple internships, but each internship is associated with only one student.
- **rating belongs to internship:** One-to-one relationship, each internship has one rating, and each rating is associated with only one internship.
- **student to rating through internship:** One-to-many relationship, each student can have many ratings as well as internships, but each rating can only have one student and one internship.
- **company to rating through internship:** One to many relationship, each company can have many ratings as well as internships, but each rating can only have one company and one internship.
:::
### Entity-Relationship Diagram
![](eer_diagram.png){fig-align="center"}
### Schema Design
![](schema_design.png){fig-align="center"}