L1

Lab Assignment: Planning Your Database

Objectives:

· Go to Course Content ( Virtual Lab. Review how to log into lab env thru https://login.daas.umuc.edu . This is not like the other dbst classes.

· Review Course Content ( Virtual Lab

· Read documents most important under Course Content ( Virtual Lab tutorials

· UMUC DBST AWS DaaS Platform 2017

· PuTTY UMUC DBST Fall 2017

· Introduction to Oracle SQL Developer v4-1-5 UMUC DBST Fall 2017

· FileZilla UMUC DBST Fall 2017

· Do not attempt to create any Databases yet. This will happen in Week 2 and instructions will be provided, when the group will create a DB instance from scratch.

· Since there is no Database yet, there will be no login in to the database using sqlplus yet.

· Test connections to putty using username - StudentFirst and password - Cyb3rl@b

· Locate data for your 10 table database.

· Create an ERD of your database

· Submit One Submission per group for Lab 1 in your respective Group File Lockers of Group folders.

· This is a group effort. Each group should work with their teams to get this done.

Procedure:

You will need to locate a set of data to use for your database. You may use any data that you find yourself from the internet, or some of the sample data that is located on the system. If you use your own data, be sure that it contains no sensitive or private information.

Each database will have ten tables that are owned by the DBA team. You will create an ERD with the 10 tables. One table should contain 50,000 rows. The other nine tables will contain 1,000 rows each. So start thinking about the number of rows. More labs to come on this. This lab is just creating the ERD. No database connections or schemas yet.

A starting set of data for six tables is located on your server, in the /u01/app/oracle/repository/dir_6tables directory. You are free to use data from these sources, or any other source (such as sample data sets on the Internet) for your tables.

The following commands demonstrate how to locate the data for the six starter tables on the umuc system which you will log in using putty.

StudentFirst> cd /u01/app/oracle/repository

StudentFirst> ls

dir_6tables

StudentFirst> cd dir_6tables

StudentFirst> ls

6tableERD.erd customer.dat order_item.dat product.dat

6tables.sql hb_zip.dat orders.ctl project.txt

ERD_6tables.doc indexSizing.txt payment.ctl sizing.txt

analyze.txt order.dat payment.dat tbspace_script.sql

customer.ctl order_item.ctl product.ctl zip.ctl

Once you have located a set of data to use for your database, you should create an entity relationship diagram (ERD) of the database. You may use a dedicated tool (such as ERWin) or a diagramming software tool (such as Microsoft Visio, ER assist) to create the diagram.

Important Notes about Lab Env useful from Week 2.

1) Please run following command after logging into unix putty as user StudentFirst.  All Labs and DB access thru sqlplus is to be done as unix  oracle user either using putty or the GUI terminal. 

1)This way oracle user can write to the /home/StudentFirst directory where most of the files will be. You run your sqlplus , sql loader from /home/StudentFirst  directory. So any logs outputs will default to that directory

2) Whenever you upload any new  file to /home/StudentFirst directory using filezilla. You need to run this command so that other users - unix user oracle ) will have full access - r w x  to these file

Commands to run -

pwd

ls -lrt

chmod 777 -R /home/StudentFirst

ls -lrt

Example

[StudentFirst@dbst670-nixora01 ~]$ pwd

/home/StudentFirst

[StudentFirst@dbst670-nixora01 ~]$ ls -lrt

total 0

drwxrwxr-x. 3 StudentFirst StudentFirst 26 Aug 22  2017 Desktop

drwxr-xr-x. 2 StudentFirst StudentFirst  6 Aug 23  2017 Downloads

drwxr-xr-x. 2 StudentFirst StudentFirst  6 Aug 23  2017 Videos

drwxr-xr-x. 2 StudentFirst StudentFirst  6 Aug 23  2017 Templates

drwxr-xr-x. 2 StudentFirst StudentFirst  6 Aug 23  2017 Public

drwxr-xr-x. 2 StudentFirst StudentFirst  6 Aug 23  2017 Pictures

drwxr-xr-x. 2 StudentFirst StudentFirst  6 Aug 23  2017 Music

drwxr-xr-x. 2 StudentFirst StudentFirst  6 Aug 23  2017 Documents

[StudentFirst@dbst670-nixora01 ~]$ chmod 777 -R /home/StudentFirst

[StudentFirst@dbst670-nixora01 ~]$ ls -lrt

total 0

drwxrwxrwx. 3 StudentFirst StudentFirst 26 Aug 22  2017 Desktop

drwxrwxrwx. 2 StudentFirst StudentFirst  6 Aug 23  2017 Downloads

drwxrwxrwx. 2 StudentFirst StudentFirst  6 Aug 23  2017 Videos

drwxrwxrwx. 2 StudentFirst StudentFirst  6 Aug 23  2017 Templates

drwxrwxrwx. 2 StudentFirst StudentFirst  6 Aug 23  2017 Public

drwxrwxrwx. 2 StudentFirst StudentFirst  6 Aug 23  2017 Pictures

drwxrwxrwx. 2 StudentFirst StudentFirst  6 Aug 23  2017 Music

drwxrwxrwx. 2 StudentFirst StudentFirst  6 Aug 23  2017 Documents

2) Review this in Week 2 after DB creation. Please note All labs need to be done as unix oracle user and not StudentFirst User.

In Lab 2 Instance creation you have screen shots ( page 24 word doc ) on how to sudo in as oracle user and connect to DB after setting the env using . oraenv

I am putting it here again. This can be done either using putty or gui terminal

command

sudo -s -u oracle

. oraenv  ( dot space oraenv , hit enter to accept the default no need to type anything )

You do not need to give @dbst670 in the connect string to access DB suing sqlplus

If you create a DB user say dbuser

you will just issue

sqlplus dbuser/password

To connect to sqlplus.