SQL Help

**Uzor** · 2014-03-09 21:43

I believe you need to use the
CONSTRAINT <primary key name> primary key (key1, key2, key3)
command in oracle. It's different depending on what you use to make your database in, but I see from the screenshot you're using oracle.

**Aylee** · 2014-03-09 21:46

Originally Posted by Uzor

I believe you need to use the
CONSTRAINT <primary key name> primary key (key1, key2, key3)
command in oracle. It's different depending on what you use to make your database in, but I see from the screenshot you're using oracle.

CREATE TABLE WorksOn
( empNo NUMBER(6) NOT NULL,
projNo NUMBER(4) NOT NULL,
dateWorked Date,
hoursWorked NUMBER(3) NOT NULL,
CONSTRAINT WorksOnBLF_FK
PRIMARY KEY(empNo, projNo, dateWorked)
);

Populated but looks like it rounded all the numbers up/down :/

Spoiler: show

EDIT: NVM fixed by changing number(3) to number(3,2)

**Aylee** · 2014-03-09 21:54

Woot all data populated lol!

Now to do the sql! If anyone has the time please look at the questions and my answers and see if you can find any holes in the logic or why it will give me wrong answers

Tables:
DepartmentBLF {deptNo, deptName, mgrEmpNo}

EmployeeBLF { empNo, fName, lName, address, dob, sex, position, deptNo}

ProjectBLF { projNo, projName, deptNo }

WorksOnBLF { empNo, projNo, dateWorked, HoursWorked }

NUMBER 1

SELECT d.deptName, COUNT(*) AS Total,
SUM(CASE WHEN e.sex = 'F' THEN 1 ELSE 0 END) AS Male,
SUM(CASE WHEN e.sex = 'M' THEN 1 ELSE 0 END) AS Female
FROM employeeBLF e NATURAL JOIN departmentBLF d
GROUP BY d.deptName
ORDER BY total

Answers half the question. Not sure how to get the sub total line at the bottom or how to re arrange like the example provides

NUMBER 2:

SELECT d.deptNo, d.deptName, e.deptNo, e.empNo, e.fName, e.lName
FROM DepartmentBLF d LEFT OUTER JOIN (SELECT *
FROM EmployeeBLF
WHERE position = 'Secretary') e
ON(d.deptNo = e.deptNo)
ORDER BY d.deptNo ASC

NUMBER 3:

SELECT deptName, empNo, fName, lName, position
FROM DepartmentBLF d CROSS JOIN EmployeeBLF e
WHERE d.mgrEmpNo = e.empNo
ORDER BY DeptName;

NUMBER 4:

SELECT empNo, fName ||' '|| lName NAME, deptName
FROM DepartmentBLF NATURAL JOIN
(SELECT *
FROM EmployeeBLF NATURAL JOIN WorksOnBLF
WHERE HoursWorked <=1)
ORDER BY deptName, lname

**Uzor** · 2014-03-09 22:30

I have to head to bed, but I'd recommend you to read up on CREATE VIEW for that. It should do what you need if I understood the questions correctly.

**Aylee** · 2014-03-09 22:47

Originally Posted by Uzor

I have to head to bed, but I'd recommend you to read up on CREATE VIEW for that. It should do what you need if I understood the questions correctly.

There was a question like it on the Second home work. I've been trying to adapt that but so far no luck >< been workin on this crap for past 4 hours though so gonna take a break for the night

Thanks for all your help

**Aylee** · 2014-03-19 17:03

Ok big test tomorrow that will make or break me. Would anyone mind explaining 1st 2nd 3ed normal forms to me? I'm totally lost on them and everything I read only confuses me more or doesn't provide examples.

**Penthesilea** · 2014-03-25 11:11

I'm just going to piggy back off this thread, since its in the same vein as the OP.

I'm a biologist thrust into new responsibilities of being an Oracle DBA as well. I've been requested to run a query to extract some meta data. Problem is one of the fields is a BLOB, which I can "view" the contents in hex binary. Problem is, I'm not sure what type of file they went into the field as (jpg, gif, some other format). Is there an easy way to figure that out or to write them out to disk somewhere?

From all my googling, it doesn't make sense that they would store the files directly in the DB, rather just store the path. I'm at loss here and any help would be appreciated!

I also looked through procedures and packages to see if there was already something set up to do just what I'm looking for, and having no luck.

**octopus** · 2014-03-25 12:15

You actually will have to write out the contents to figure out what the blob is.

BUT, better yet, just ask someone who knows the schema? I'm assuming there is no documentation that you can just look up.

**Aylee** · 2014-03-26 17:32

Ok doing a big case study. I have to make the functional dependencies. This is what my Partner Sent me:

Spoiler: show

These are my dependancies

Spoiler: show

Did i cover everything? Is there anything I'm missing or that i have that shoulden be there?

**Tajin** · 2014-03-26 18:01

Originally Posted by Penthesilea

I'm just going to piggy back off this thread, since its in the same vein as the OP.

I'm a biologist thrust into new responsibilities of being an Oracle DBA as well. I've been requested to run a query to extract some meta data. Problem is one of the fields is a BLOB, which I can "view" the contents in hex binary. Problem is, I'm not sure what type of file they went into the field as (jpg, gif, some other format). Is there an easy way to figure that out or to write them out to disk somewhere?

From all my googling, it doesn't make sense that they would store the files directly in the DB, rather just store the path. I'm at loss here and any help would be appreciated!

I also looked through procedures and packages to see if there was already something set up to do just what I'm looking for, and having no luck.

Don't know much about oracle but in MS SQL the blobs usually have additional columns to indicate the file type.

**Penthesilea** · 2014-03-27 10:00

Originally Posted by Tajin

Don't know much about oracle but in MS SQL the blobs usually have additional columns to indicate the file type.

Well, it should all be the same file type, but I have no idea what that type is. I'm new, taking over for a guy who just up and left, so I've had no training and on top of that, the DBA was worthless.

There's two fields that should have the same information, the OBJSSTRCT and OBJSMOLFILE, the first being a blob, the second a clob. The clob isn't "null" when I return the data, but when I try to use dbms_lob.read, I also get an error (see the first image in spoiler).

Spoiler: show

my quick read query of the CLOB.

Spoiler: show

**Uzor** · 2014-03-27 11:10

Originally Posted by Aylee

Ok doing a big case study. I have to make the functional dependencies. This is what my Partner Sent me:

Spoiler: show

These are my dependancies

Spoiler: show

Did i cover everything? Is there anything I'm missing or that i have that shoulden be there?

I'm having a hard time following this, is the first one a logical notation? It doesn't have any primary keys etc. so it's hard to tell.

Have you made a class diagram for it you can include, too?

Edit: Not sure if you've done a class diagram, but I made one that I believe will help. I did it hastily, so it's not perfect and looks kind of cluttered, but you can take it and work on it. It should answer the questions you need, as it makes the dependencies rather obvious.

I had to make some edits though as it had some null (a big no-no in most cases as it makes for "ugly" and inefficient databases), but I had to leave one of them in, which is the actualOutDate in the Inpatient class, as I'm not sure what your exact project is. If possible, I would recommend making it a reified class to avoid null, or moving it somewhere else. You could also, if possible, completely remove it, since that information would already exist in "outpatient" through aDate and aTime. My recommendation would be to make it a reification though, as you may need that information (for insurance purposes or whatever), and an inpatient does not necessarily become an outpatient.

There are some other unclears as I haven't read the project text. For example,;
1) Can a staff member be employed at more than one ward within the same clinic, or does he/she specifically work in one clinic?

2) Does a doctor also have a staffNo? (According to your partner's notation, a doctor does not have a staffNo, but logically he should have, which is what I show in the diagram. If, however, a doctor does not have a staffNo, then the staffNo attribute should be moved out of the StaffMember class and instead be moved in to the classes that needs it, like Nurse).

3) Can a staff member work at more than one clinic? I assume not, since the doctor is tied to a specific clinic ID in your logical notation. But I'm sure you can figure it out.

4) Can SurgSupplies and PharmSupplies be purchased from more than one supplier? If not; 1..1 -> 0..* multiplier. Otherwise, 1..* -> 0..* multiplier.

From the class diagram you can then turn it in to a logical notation, which will show the dependencies, unless your teacher is asking for some other form of notation to show the dependencies, as a logical notation makes dependencies very clear. Take the relationship between Person, StaffMember, Clinic and Doctor, for example

Person.(fName, lName, homeAddress, phoneNumber)

StaffMember.(fName, lName, homeAddress, phoneNumber, staffNo, gender)
StaffMember.(fName, lName, homeAddress, phoneNumber) <- Person.(fName, lName, homeAddress, phoneNumber)
AK1 StaffMember.(fName, lName, homeAddress, phoneNumber)

Clinic.(clinicNo, clinicAddress, clinicPhone, clinicFax, clinicName)

Doctor.(fName, lName, homeAddress, phoneNumber, staffNo, gender, clinicNo)
Doctor.(staffNo) <- staffMember.(staffNo)
Doctor.(clinicNo) <- Clinic.(clinicNo)
AK1 Doctor.(fName, lName, homeAddress, phoneNumber)

I'm not familiar with the way you wrote it out, so I can't tell if it's accurate or not. Whats worse is, there's squares on my computer, so I can't tell if there's supposed to be arrows or some other sign

Or is it written out as a table? can't tell xD Although it might be partly because I just came home from vacation and my brain is a mush from the travelling.

Edit: I looked over it and I think I figured it out. Sorry, my head is a mush, so it took me a while, I assume the squares are supposed to show which one is the primary key, and the other ones show the dependencies. It looks OK, although it still doesn't solve some of the issues you'll run in to later with null values. I would recommend a Clinic class that Doctor is a dependency to, and Ward has a dependency to Clinic. I would also recommend a reification of actualOutDate with a dependency to PatientNo to get rid of that problem, since you really do not want 0..1 or 0..* multiplicities on any attributes in tables. NULL values can cause a lot of problems when it comes to pulling out data, comparing data, sorting data etc. For example, joining tables together can produce unexpected results, aggregation WILL give you unexpected results without an error code if any of them have null values, etc. It makes programming very unsafe as you're never sure whether or not you can be confident that the data exists or not. NULL values can be used, and can be useful under the right circumstances, but I'm not sure if this is one of them - especially since it would be easier from a programming-standpoint with a reification.

Thread: SQL Help

Thread Tools

Similar Threads

SQL Database - Help studying for Test

SQL Help

SQL help

SQL help

Latest Threads

Up & Coming Threads

Hottest Threads