Urgent! Broly's with Microsoft Access Experience!

[Ffactor]

Need help with this stinking project! Help a brotha out!

INSS 640 - Information Systems and Technology
Project #2

Database Assignment

This is an individual (as opposed to group) assignment. However, while the work must be your own, I encourage students to cooperate as far as helping each other work with the database software applications. As shown on the Course Calendar web page – this project is due at the beginning of class on the date shown on the course calendar.

Using Microsoft Access (which is available in the University computer labs), you are to build a small database system. You can choose any (small) business application you like, a hobby, or non-profit club, etc. An example is provided at the bottom of the page.

The deliverables for the database you will build are:

A description of the database, its purpose, and its requirements
An entity-relationship diagram (ERD) for the system and a data dictionary that includes the fields/attributes of each entity
The database itself. Fancy graphics and formatting are not required. The database must match the requirements and ERD you created for the system. It should allow the user to input all necessary data (i.e. update all fields/records) using a graphical user interface (e.g. forms) and to generate some basic reports. The nature of the reports will depend on the system you choose and its requirements, but they should meet obvious needs. The key issue is the quality of your requirements, the data model you build, and whether the database corresponds to them.
We will cover Microsoft Access in class. However, you may need to research Microsoft Access from outside sources to finish this assignment. There are several Microsoft Access sources in the library and on the web. For example,


Example Application: Wine Club (do not use this example as your project - you should come up with your own application)

An example system would be building a small database for a wine club secretary. The way the club works is as follows: members join and pay a $50 entry fee. The club has a selection of wines, which members may order at the club member price (which is cheaper than retail). Each wine has an individual bottle price and a "by the dozen" price. Some popular wines are bought by the club and are kept in the wine cellar of the club; others the club orders in from its suppliers when a club member orders it. Members must have enough money deposited into the club to cover any order they make (i.e., either their current balance is enough, or the order comes with a check/cash). $40 of the original cost of membership is applied to the member’s balance.

The system must allow the Secretary of the club to do the following:

Enter membership details, including the name address and membership balance
Enter inventory details, including name, description, price, rating, amount on hand, of all the wines the club sells
Enter order details, including member name, the items they have selected, the quantity, estimated delivery date, etc.
Print reports of use to the Secretary. You should describe the business use/purpose of the reports. One report should contain data from more than one table.

 

[slickdadd]

I sure hate using Access but that doesn't sound too difficult.

 

[Ffactor]

I sure hate using Access but that doesn't sound too difficult.

It's all chinese to me!

 

[slickdadd]

Just use the wizards in Access and it basically does half of it for you

 

[Ffactor]

Ok, will try!

 

[javaguru]

The project isn't a difficult one. To get in the proper mindset try sitting down and imagining how you would keep track of the data if all you had were a pencil and paper. Then think about how you would transfer it into a database format. In the real world requirements will likely change over time so you want the database to be as scalable and extensable as possible with changes requiring little or no effort. However, the concept may be a little more advanced for this project but it never hurts to incorporate advanced concepts.

 

[the nature boy]

sounds like Subway University is a little more complex than ffactor thought.

 

[AAP]

Just use the wizards in Access and it basically does half of it for you

YES! The Wizards will take care of the reports and you won't have to hand code the SQL aspects.

Designing the input forms is relatively easy. It sounds really plain. Maybe 1 hour tops. I mean, the hardest part is going to be the typing out of the data dictionary and assembling the ERD. (Use Visio, it will make it easy). The actual Access part is about 15 minutes of time afterwards.

 

[Ffactor]

YES! The Wizards will take care of the reports and you won't have to hand code the SQL aspects.

Designing the input forms is relatively easy. It sounds really plain. Maybe 1 hour tops. I mean, the hardest part is going to be the typing out of the data dictionary and assembling the ERD. (Use Visio, it will make it easy). The actual Access part is about 15 minutes of time afterwards.

What's SQL and Visio!

 

[Ffactor]

Ok, I used access to create this order entry system. Anybody know how I can modify it so if a person orders a certain quantity a discount rate will show up?

 

[oubeta]

DO you have to do it from SQL? In all my database classes we had to write the SQL first, we couldn't use a wizzard,,that's cheating. lol

I think I might have a similar assigment for you to look at. I check later this evening.

 

[oubeta]

# this world must be used against a database
# containing EMP and DEPT tables defined as follows:
# CREATE TABLE DEPT
# (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
# DNAME VARCHAR2(14) ,
# LOC VARCHAR2(13) ) ;
# CREATE TABLE EMP
# (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
# ENAME VARCHAR2(10),
# JOB VARCHAR2(9),
# MGR NUMBER(4),
# HIREDATE DATE,
# SAL NUMBER(7,2),
# COMM NUMBER(7,2),
# DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

WorldInfo {
info [ "Boomer Sooner",
"Created by OUBETA"
}

# three spheres represent the first three Departments

# on initialization, the names of the Spheres are
# set to the department names

# clicking on a Sphere will display the names and
# salaries of all employees in that department

DEF PlainText FontStyle {
family "SANS"
style "BOLD"
justify "FIRST"
size 2
spacing 1
}

NavigationInfo {
type [ "EXAMINE", "ANY" ]
}

Viewpoint
{
position 0 -12 60
}

PROTO DeptSphere [
exposedField MFString name ""
eventOut SFString nameClicked
]
{
Group {

children [

DEF TS TouchSensor {
}

DEF outputName Script {
eventIn SFBool generate
eventOut SFString nameClicked IS nameClicked
eventIn MFString name_changed
field MFString name ""
url "javascript:
function generate(value)
{
if (value == true)
{
nameClicked = name[0];
}
}

function name_changed(value)
{
name = value;
}"
}


Shape {
appearance Appearance {
material Material {
diffuseColor 0 0 1
}
}
geometry Sphere {
radius 5
}
}

Transform {
translation 0 8 0
children [
Shape {
appearance Appearance {
material Material {
diffuseColor 1 1 1
}
}
geometry DEF theName Text {
fontStyle FontStyle {
family "SANS"
style "BOLD"
justify "MIDDLE"
size 2
spacing 1
}

string IS name
}
}

]
}


]

ROUTE TS.isActive TO outputName.generate
ROUTE theName.string_changed TO outputName.name_changed

}
}

# the four departments

Transform {
translation -15 0 0
children [
DEF DEPT1 DeptSphere {}
]
}

Transform {
translation 0 0 0
children [
DEF DEPT2 DeptSphere {}
]
}

Transform {
translation 15 0 0
children [
DEF DEPT3 DeptSphere {}
]
}


# these nodes display the selection results

Transform {
translation -5 -15 0
children [
Shape {
appearance DEF white Appearance {
material Material {
diffuseColor 1 1 1
}
}
geometry DEF enames Text {
fontStyle USE PlainText
string [ ]
}
}
]
}

Transform {
translation 5 -15 0
children [
Shape {
appearance USE white
geometry DEF sals Text {
fontStyle USE PlainText
string [ ]
}
}
]
}

# on startup, connect to the database
DEF init Script {
eventOut SFBool start
url "javascript:
function initialize() {
start = true;
}"
}

DEF connect Script {
eventIn SFBool execute
# modify the following connect string for your database
field SFString sql "connect jdbcracle:thin:scott/[email protected]:1516:ORA7"
eventOut SFInt32 result
url "execsql.class"
}

# check that the connection succeeded
DEF connectResult Script {
eventIn SFInt32 result
eventOut SFBool selectDepts
url "javascript:
function result(value) {
if (value == 0)
{
Browser.setDescription('Connected to database');
selectDepts = true;
}
else
Browser.setDescription('Unable to connect to database: error code is ' + value);
}"
}

# if the connection succeeded, select the first four department names
DEF selectDepts Script {
eventIn SFBool execute
eventOut MFString dname
field SFString sql "for 3 select dname into :dname from dept order by deptno"
url "execsql.class"
}

# process the department names by setting
# the names of each of the DeptSphere objects

DEF processDnames Script {
eventIn MFString dnames
eventOut MFString dname1
eventOut MFString dname2
eventOut MFString dname3
url "javascript:
function dnames(value) {
dname1 = new MFString(value[0]);
dname2 = new MFString(value[1]);
dname3 = new MFString(value[2]);
}"

}


# this script simply generates an SFBool event to execute the
# select script
DEF prepareSelect Script {
eventIn SFString deptname
eventOut SFBool doSelect
url "javascript: function deptname(value)
{
doSelect = true;
}"
}

# select the names and salaries of all employees in
# the appropriate department
DEF select Script {
eventIn SFBool execute
eventIn SFString deptname
field SFString sql "select ename, sal into :ename, :sal from emp, dept where emp.deptno=dept.deptno and dept.dname = :deptname"
eventOut MFString ename
eventOut MFString sal
eventOut SFString errorMessage
url "execsql.class"
}

# check that the select statement succeeded
DEF selectResult Script {
eventIn SFString errorMessage
url "javascript:
function errorMessage(value) {
if (value != '')
{
Browser.setDescription('A SQL error occurred: ' + value);
}
}"
}

ROUTE init.start TO connect.execute
ROUTE connect.result TO connectResult.result
ROUTE connectResult.selectDepts TO selectDepts.execute
ROUTE selectDepts.dname TO processDnames.dnames
ROUTE processDnames.dname1 TO DEPT1.set_name
ROUTE processDnames.dname2 TO DEPT2.set_name
ROUTE processDnames.dname3 TO DEPT3.set_name

ROUTE DEPT1.nameClicked TO select.deptname
ROUTE DEPT2.nameClicked TO select.deptname
ROUTE DEPT3.nameClicked TO select.deptname

ROUTE DEPT1.nameClicked TO prepareSelect.deptname
ROUTE DEPT2.nameClicked TO prepareSelect.deptname
ROUTE DEPT3.nameClicked TO prepareSelect.deptname

ROUTE prepareSelect.doSelect TO select.execute

ROUTE select.ename TO enames.set_string
ROUTE select.sal TO sals.set_string

Here is an example of SQL code.
This didn't cut very good, I'll help you when I get back. I think this is a little deeper that you have to go on yours.

 

[Ffactor]

I have no idea what an SQL is or how to write one.

 

[the nature boy]

#holy shit
{you're fucked}
ep op orp
END

 

[Ffactor]

Oubeta, this is supposed to be an introductory class on information systems for business majors. That stuff looks hedious!

 

[Ffactor]

#holy shit
{you're fucked}
ep op orp
END

Gay!

 

[oubeta]

Have you gone over ERD diagrams, primary keys, etc yet?
This is pretty simple. Ya you might even get away with wizard. If it is an SQL assigment the SQL the wizard puts in will be different and the teacher can tell you used wizard to write it. It puts in all kinds of extra stuff. I have know idea why it does that.

YOu should just be able to use the build function in your queries and tables. Define the criteria in there. Man you guys have it easy. We had to do ours in straight SQL. I don't even know really how to tell you to do it unless it's SQL.

Have you got a working ERD diagram yet with the primary keys defined with all the entities?