Room for improvement?

cluelessPHP

So I spent today working on these questions to improve my sql, could the answers be improved? tables and answers below

CREATE TABLE IF NOT EXISTS `branch` (
  `division` int(11) NOT NULL DEFAULT '0',
  `divname` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`division`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `branch`
--

INSERT INTO `branch` (`division`, `divname`, `city`) VALUES
(10, 'Admin', 'London'),
(20, 'Sales', 'Bristol'),
(30, 'Consulting', 'London'),
(40, 'Training', 'Manchester'),
(50, 'Computing', 'Birmingham');

-- --------------------------------------------------------

--
-- Table structure for table `car`
--

CREATE TABLE IF NOT EXISTS `car` (
  `snum` int(11) DEFAULT NULL,
  `regno` varchar(9) NOT NULL DEFAULT '',
  `make` varchar(20) DEFAULT NULL,
  `model` varchar(20) DEFAULT NULL,
  `mileage` decimal(10,0) DEFAULT NULL,
  PRIMARY KEY (`regno`),
  KEY `car_fk1` (`snum`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `car`
--

INSERT INTO `car` (`snum`, `regno`, `make`, `model`, `mileage`) VALUES
(3107, 'G343ALJ', 'BMW', '316i', '27216'),
(3200, 'F260PQN', 'Ford', 'Sierra', '30986'),
(3414, 'E329WUF', 'Volvo', '340', '28216'),
(3698, 'G181BSS', 'Ford', 'Sierra', '18311'),
(3724, 'G213CBS', 'Ford', 'Orion', '20016'),
(3788, 'G268JFN', 'Volvo', '440', '16485'),
(3812, 'H989TNP', 'Toyota', 'Lexus', '2193'),
(3813, 'F138RUS', 'BMW', '320', '28726');

-- --------------------------------------------------------

--
-- Table structure for table `staff`
--

CREATE TABLE IF NOT EXISTS `staff` (
  `snum` int(11) NOT NULL DEFAULT '0',
  `surname` varchar(10) DEFAULT NULL,
  `jobtitle` varchar(10) DEFAULT NULL,
  `super` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `joindate` date DEFAULT NULL,
  `division` int(11) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  `bonus` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`snum`),
  KEY `staff_fk1` (`division`),
  KEY `staff_fk2` (`super`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`snum`, `surname`, `jobtitle`, `super`, `sex`, `joindate`, `division`, `salary`, `bonus`) VALUES
(3926, 'Towlson', 'Chairman', NULL, 'F', '1984-07-11', 10, 45816, NULL),
(3407, 'Macrae', 'Secretary', 3926, 'F', '1982-12-13', 10, 16200, NULL),
(3812, 'Khan', 'Director', 3926, 'M', '1982-12-14', 20, 42000, 950),
(3813, 'Bate', 'Director', 3926, 'F', '1970-04-20', 10, 46752, NULL),
(3200, 'Raines', 'Manager', 3812, 'F', '1980-07-01', 20, 25872, 500),
(3414, 'Welham', 'Manager', 3813, 'M', '1987-06-22', 10, 25872, NULL),
(3107, 'Hamilton', 'Salesman', 3200, 'M', '1982-12-13', 20, 18534, 4500),
(3215, 'Tringham', 'Clerk', 3200, 'M', '1981-03-16', 20, 9384, NULL),
(3016, 'Brock', 'Secretary', 3414, 'F', '1983-06-11', 10, 12288, NULL),
(3365, 'Hughes', 'Accountant', 3414, 'M', '1983-11-17', 10, 23760, NULL),
(3788, 'Kumar', 'Manager', 3813, 'M', '1975-02-10', 30, 30816, 1586),
(3488, 'Styles', 'Clerk', 3365, 'F', '1982-12-01', 10, 8652, NULL),
(3698, 'French', 'Consultant', 3788, 'M', '1980-04-01', 30, 20184, 0),
(3724, 'Adye', 'Consultant', 3788, 'M', '1969-10-28', 30, 29400, 825),
(3989, 'Bray', 'Consultant', 3788, 'M', '1973-09-24', 30, 18000, 825);

Answers :
1. Find the registration number and mileage for each FORD car

SELECT mileage, model, regno FROM car WHERE make = 'Ford'

2. List all the different job titles held by females.

SELECT jobtitle FROM staff WHERE sex = 'F'

3. Show for each vehicle registration and make, the branch name and city as well.

SELECT regno, make, divname, city FROM car c, branch b, staff s 
WHERE s.division=b.division AND s.snum=c.snum

4. Find the salaries for London personnel.

SELECT salary, city FROM staff s, branch b
 WHERE b.city = 'London' AND b.division=s.division

5. Find personnel details ordered by division, job title and salary (highest first).

SELECT surname, salary,jobtitle, b.division FROM staff s, branch b 
WHERE b.division=s.division
ORDER BY b.division, salary DESC

Exercise 3 – moderate queries 6. Find those personnel who started between 1983 and 1987 and have no bonus (Hint: Use the SQL reserved words BETWEEN, AND, IS NULL).

SELECT surname FROM staff WHERE bonus IS NULL AND joindate BETWEEN '83-01-01' and '87-12-31'

7. How many vehicles are there in total? And how many are there of each make? Two separate queries.

SELECT count(make) from car ; 
SELECT count(make) from car group by make;

8. Find the average and maximum salaries for consultants.

SELECT MAX(salary), AVG(salary) FROM staff

9. Find staff whose salaries exceed the mileage of the car they drive and by how much! (silly query but ……………!!!!!)

SELECT surname, mileage-salary FROM staff s, car c WHERE mileage>salary AND s.snum=c.snum

10. What is the average, maximum and minimum salary for each division name (use suitable aliases for your output)?

SELECT division, MAX(salary) AS Highest, AVG(salary) AS Average, MIN(salary) AS Lowest FROM staff
GROUP BY division

Find the staff names and division names for those who drive a FORD with registration numbers starting with G.

SELECT surname, divname from staff s,branch b,car c where make='Ford' and regno like 'G%' and b.division=s.division and s.snum=c.snum;

Find the total staff cost for the sales division; use NVL(Bonus,0) to convert null bonuses. Exercise

select salary+coalesce(bonus,0) from staff s, branch b where divname='sales' and s.division=b.division

How many staff does each staff member manage? Your query will probably not show staff who do not manage. Modify the query to include them too.
```
SELECT s.snum, s.surname, COUNT(s.snum) AS manager FROM staff s, staff s2 Where s.snum=s2.super GROUP BY s.snum,s.surname;
```
Show the manager code and number of staff managed for staff who manage at least 2 others.
```
SELECT super, COUNT(snum) as manaer  FROM staff GROUP BY super  HAVING COUNT(snum)>=2
```

Print staff details with the same job title but different divisions.

SELECT s.surname, s2.surname,s.jobtitle,s.division,s2.division FROM staff s, staff s2 WHERE s.jobtitle=s2.jobtitle AND NOT(s.division=s2.division);

How many staff are managed by Kumar?

SELECT count(*) as count FROM staff s, staff s2 WHERE s.surname='Kumar' and s.snum=s2.super;

Show the full details of all staff who are not managers.

SELECT s.* FROM staff s LEFT JOIN staff s2 ON s.snum=s2.super WHERE s2.super is null;

Show division code and name, average salary and range of salaries (highest minus lowest) for the branches with average salary above 25000.

SELECT p.division,b.divname, AVG(p.salary) AS Average, MAX(p.salary)-MIN(p.salary) difference FROM staff p, branch b where p.division=b.division GROUP BY p.division,b.divname HAVING AVG(salary)>25000;

NogDog

Just a couple things that jumped out at me:

You might want a "SELECT DISTINCT" for #2.

Date strings should use 4-digit years to avoid ambiguity/errors (#6).

Stylistically, my personal preference is to always explicitly state the joins, as opposed to using a comma-separated list of tables.

cluelessPHP

Oh, I thought commas might be more efficient way of running queries, can understand about "SELECT DISTINCT" it makes more sense

NogDog

cluelessPHP;11057603 wrote:
Oh, I thought commas might be more efficient way of running queries, can understand about "SELECT DISTINCT" it makes more sense

Generally speaking, computers are really, really fast these days. Therefore, even if one notation actually is faster than another (and I don't know that it actually would be in this case or not), you're talking about microseconds, most likely. I'm generally in favor of coding for readability first, as the time it takes for developers to maintain code is generally more expensive than the hardware it runs on, so I like to optimize things in favor of ease of maintenance -- while avoiding obviously poor-performing code, mind you.

cluelessPHP

So the more generic it is the better? I mean if you can use a function to for example sanitise all input use that rather than writing code for each input

Weedpacket

NogDog wrote:
Therefore, even if one notation actually is faster than another (and I don't know that it actually would be in this case or not), you're talking about microseconds, most likely.

In the case of inner joins, DBMSs would likely generate exactly the same query plan for both explicit ANSI-style JOIN syntax as for "list of WHERE clauses". They ought to, if the two statements are equivalent and the planner is any good! PostgreSQL certainly does (runtime configuration twiddling notwithstanding).

To get other join types (LEFT/RIGHT, FULL, NATURAL) without using JOIN syntax would probably involve gluing multiple queries together with INTERSECT or UNION.

(And right now I'm trying to remember the book I read that did a really good job of providing a roadmap for normalising to BCNF; mainly by deriving that form directly instead of pushing the design through 1NF, 2NF and 3NF first. When you design a modern car you do it directly - you don't start with a Benz Velo design and then modify it.)

cluelessPHP

Need to learn BCNF apparently, I can kind of do to 3rd

NogDog

cluelessPHP;11057607 wrote:
So the more generic it is the better? I mean if you can use a function to for example sanitise all input use that rather than writing code for each input

Another aspect of the maintainability objective: "Keep it DRY." (Don't Repeat Yourself). My earlier reply about the joins was more just simple source code style. I also like to write any but the shortest SQL on multiple lines with indentation, which both makes it easier to read, and easier to debug when the DB error message points out an error on "line number X" of the SQL -- which if X is always 1, it becomes a useless bit of info. 🙂 Instead of this...

SELECT p.division,b.divname, AVG(p.salary) AS Average, MAX(p.salary)-MIN(p.salary) difference FROM staff p, branch b where p.division=b.division GROUP BY p.division,b.divname HAVING AVG(salary)>25000;

...I'd prefer this...

SELECT
  p.division,
  b.divname,
  AVG(p.salary) AS average, 
  MAX(p.salary) - MIN(p.salary) AS difference
FROM staff p
INNER JOIN branch b ON p.division = b.division
GROUP BY p.division, b.divname
HAVING AVG(salary) > 25000;

PS: since the tables are joined on columns with the same names, you could make it a wee bit simpler with:

INNER JOIN branch b USING(division)

NogDog

Weedpacket;11057611 wrote:
In the case of inner joins, DBMSs would likely generate exactly the same query plan for both explicit ANSI-style JOIN syntax as for "list of WHERE clauses". They ought to, if the two statements are equivalent and the planner is any good! PostgreSQL certainly does (runtime configuration twiddling notwithstanding)....

I was actually only thinking about whether the SQL parser would be any faster getting from one syntax or the other to the actual query plan the DBMS uses. Sort of on the same micro-optimization worries of people who argue over whether to use double quotes with embedded PHP variables or single quotes with concatenated variables. 🙂

Weedpacket

cluelessPHP;11057613 wrote:
Need to learn BCNF apparently, I can kind of do to 3rd

Search for "Boyce-Codd decomposition". There are algorithms for doing it to existing sets of relations that leave them in BCNF.
Mind you, they expect you to know what "nontrivial functional dependencies" and "superkeys" are, first....

I think BCNF is generally a sufficient target for normalisation with 3NF as a fallback: sometimes you can't decompose a relation while preserving one dependency without breaking another, so you have to ask yourself which one is more important to what you're modelling (preserve the dependency vs. tolerate duplicated/redundant data (that might need application-level support to maintain consistency)). Note that some algorithms provide enough wiggle room that you can get several different BCNF decompositions from the same model.

4-, 5-, and 6NF are really starting to get hairy: the conditions they cover are pretty rare to begin with, and accommodating them can make the model quite a bit more complex (and 6NF can lead to a combinatorial explosion in tables - like, a table for each attribute listing all its possible values!), so it becomes a growing struggle to find a situation where they're worth the effort.

cluelessPHP

UNF

ItemCode
StockNumber
SupplierCode
SupplierAddress
Description 
Size
DailyHireRate
CustomerId
CustomerName
CustomerPostCode
CustomerTelno
HireDate
ExpectedReturnDate
Paid/Unpaid

Identify repeating attributes

1NF

Customer table 

CustomerId(pk)
CustomerName
CustomerPostCode
CustomerTelno

Store table 

SupplierCode(PK)
ItemCode
StockNumber
SupplierAddress
Description 
Size
DailyHireRate
HireDate
ExpectedReturnDate
Paid/Unpaid

Remove any non-key attributes that only depend on part of the table key to a new table

2NF

Customer table 

CustomerId(pk)
CustomerName
CustomerPostCode
CustomerTelno

Supplier table

SupplierCode(PK)
SupplierAddress

Item  table 

ItemCode
Description
Size
DailyHireRate
HireDate
ExpectedReturnDate
Paid/Unpaid

Customer table 

CustomerId(pk)
CustomerName
CustomerPostCode
CustomerTelno

Remove to a new table any non-key attributes that are more dependent on other non-key attributes than the table key

3NF

Customer table 

CustomerId(pk)
CustomerName
CustomerPostCode
CustomerTelno

Customer details

CustomerName
CustomerTelno


SupplierCode(PK)
SupplierAddress



Item table 
ItemCode(PK)
Description
Size
DailyHireRate

Customer orders

CustomerId(fk)
ItemCode(fk)
HireDate
ExpectedReturnDate
Paid/Unpaid

IF I understood correctly, this shouldn't need more than 3NF unless CustomerPostCode + CustomerTelno can also be considered a candidate key, which would be put into another table?

Weedpacket

Well, as I said, I prefer to skip the 1NF/2NF stuff and go straight to finding functional dependencies and splitting relations as needed to make superkeys out of them (result: BCNF unless rejoining the split relations would lead to bogus tuples, in which case the result would be 3NF).

cluelessPHP

I was looking at some exam papers from previous years they have questions like...

The table below fails 2nd Normal Form (2NF).

EmpID Skill Location Rate £
1 Databases Glasgow 27
1 Programming Glasgow 35
2 IT Glasgow 40
3 Databases Edinburgh 26

Answer these questions:

i. Identify a suitable primary key for the above table
ii. Explain why the table fails 2NF.
iii. Normalise the table so that it passes 2NF
iv. Comment briefly on whether your new tables pass 3rd Normal Form.

So it might come up again so I should prepare for it