Write DML Statements to simulate the following Business Processes: 1.Setup a dep
ID: 3910375 • Letter: W
Question
Write DML Statements to simulate the following Business Processes:
1.Setup a department, course within a department with 2 sections. Section must have a location assigned. Produce a report showing the department, its course and sections (with complete section information).
2.Now register a student to a section and process student payment. Produce a report showing student registration information, including payment information.
How can you improve your DB Schema further. Make four recommendations.
Explanation / Answer
Q1) .Setup a department, course within a department with 2 sections. Section must have a location assigned. Produce a report showing the department, its course and sections (with complete section information)
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2) check (budget > 0),
primary key (dept_name)
);
create table course
(course_id varchar(8),
title varchar(50),
dept_name varchar(20),
credits numeric(2,0) check (credits > 0),
primary key (course_id),
foreign key (dept_name) references department
on delete set null
);
create table section
(course_id varchar(8),
sec_id varchar(8),
semester varchar(6)
check (semester in ('Fall', 'Winter', 'Spring', 'Summer')),
year numeric(4,0) check (year > 1701 and year < 2100),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),
primary key (course_id, sec_id, semester, year),
foreign key (course_id) references course
on delete cascade,
foreign key (building, room_number) references classroom
on delete set null
);
REPORT QUERY:-
SELECT dept_name, building, budget, course_id, title, credits, sec_id, semester, year FROM department JOIN course on department.dept_name = course.dept_name JOIN section ON course.course_id = SECTION.course_id ORDER BY dept_name ASC;
Q2) Now register a student to a section and process student payment. Produce a report showing student registration information, including payment information.
SELECT studid, name, dept_name, firstname, lastname, fess_paid, semester, date_of_pay from student join department on student.dept_name = department.dept_name join payment on student.studid = payment.studid order by studid ASC;
How can you improve your DB Schema further. Make four recommendations.
1. Have an Integer Primary Key:- Even if you’re using UUIDs or it doesn’t make sense (e.g. for join tables), add the standard id column with an auto-incrementing integer sequence. This kind of key makes certain analyses much easier, like selecting only the first row of a group. And if an import job ever duplicates data, this key will be a life-saver because you’ll be able to delete specific rows.
2. Be Consistent with Foreign Keys - There are many styles for naming primary and foreign keys. My recommendation, and the most popular, is to have a primary key called id for any table foo, and have all foreign keys be named foo_id.
3. Store Datetimes as Datetimes - Don’t store Unix timestamps or strings as dates: convert them to datetimes instead. While SQL’s date math functions aren’t the greatest, doing it yourself on timestamps is even harder. Using SQL date functions requires every query to involve a conversion from the timestamp to a datetime.
4. Don’t Over-Normalize - Dates, zip codes, and countries don’t need their own tables with foreign key lookups. If you do that, every query ends up with a handful of the same joins. It creates a lot of duplicated SQL and a lot of extra work for the database.
Please let me know in case of any clarifications required. Thanks!
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.