Wednesday, January 28, 2015

Word and Dictionary Example

Here is the code for the WordClass

package com.spconger.DictionaryProgram;

public class Word {
 /*************************
  * this class store the content
  * of a word and definition
  * It has a toString() method
  * that concatenates the word and
  * a definition with a dash between
  */
 //private fields
 private String word;
 private String definition;
 
 public String getWord() {
  return word;
 }
 public void setWord(String word) {
  this.word = word;
 }
 public String getDefinition() {
  return definition;
 }
 public void setDefinition(String definition) {
  this.definition = definition;
 }
 
 //this overrides the toString method
 //that comes from object
 public String toString(){
  return getWord() + "--" + getDefinition();
 }

}

Here is the MyDictionary class

package com.spconger.DictionaryProgram;

import java.util.ArrayList;

public class MyDictionary {
 /*****
  * this class stores the Word objects
  * in an Arraylist. It provides methods
  * for adding words, removing them and 
  * returning the whole list of words
  */
     private ArrayList<Word> words;
     
     //initialize the ArrayList in the constructor
     public MyDictionary(){
      words = new ArrayList<Word>();
     }
     
     //add words to the list
     public void addWord(Word w){
      words.add(w);
     }
     
     //remove words from the list
     public void removeWord(Word w){
      for(Word wd : words){
       if(wd.getWord().equals(w.getWord())){
        words.remove(wd);
       }//end if
      }//end for
     }//end removeword
     
     //return the whole list of words
     public ArrayList getWords(){
      return words;
     }
}

Here is the Form Again

package com.spconger.DictionaryProgram;

//import allthe various form elements
import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.ArrayList;
import javax.swing.DefaultListModel;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextField;

public class WordForm {

 /***************
  * this class creates the form to add 
  * words and display the results in a 
  * JList object.  The form contains
  * four panels: A border panel to orient
  * the others, A grid panel that contains
  * labels and textboxes, a scrollpane 
  * with a list in the center and a 
  * flow panel with the buttons at
  * the bottom
  */
 private JFrame frame;
 private JPanel borderPanel;
 private JPanel newWordPanel;
 private JPanel buttonPanel;
 private JScrollPane scrollPane;
 private JList wordList;
 private JLabel wordPrompt;
 private JTextField wordText;
 private JLabel defPrompt;
 private JTextField defText;
 private JButton addButton;
 private JButton getWordsButton;
 private JButton exitButton;

 //instantiate the dictionary class
 private MyDictionary tech;

 public WordForm() {
  createFrame();
  tech = new MyDictionary();
 }

 private void createFrame() {
  frame = new JFrame();
  frame.setBounds(100, 100, 300, 300);
  frame.add(createBorderPanel());
  frame.setVisible(true);
 }

 private JPanel createBorderPanel() {
  borderPanel = new JPanel();
  borderPanel.setLayout(new BorderLayout());
  borderPanel.add(createNewWordPanel(), BorderLayout.NORTH);
  borderPanel.add(createScrollPane(), BorderLayout.CENTER);
  borderPanel.add(createButtonPanel(), BorderLayout.SOUTH);
  return borderPanel;
 }

 private JPanel createNewWordPanel() {
  newWordPanel = new JPanel();
  newWordPanel.setLayout(new GridLayout(2, 2));
  wordPrompt = new JLabel("Enter Word");
  wordText = new JTextField();
  defPrompt = new JLabel("Enter Definition");
  defText = new JTextField();
  newWordPanel.add(wordPrompt);
  newWordPanel.add(wordText);
  newWordPanel.add(defPrompt);
  newWordPanel.add(defText);
  return newWordPanel;
 }

 private JScrollPane createScrollPane() {
  wordList = new JList();
  // add the selection listener to the list
  // wordlist.addListSelectionListener(new SelectionListener());
  scrollPane = new JScrollPane(wordList);
  scrollPane.setBounds(20, 20, 100, 200);

  return scrollPane;
 }

 private JPanel createButtonPanel() {
  buttonPanel = new JPanel();
  buttonPanel.setLayout(new FlowLayout());
  addButton = new JButton("Add Word");
  addButton.addActionListener(new AddButtonListener());
  getWordsButton = new JButton("Get Words");
  getWordsButton.addActionListener(new GetWordsListener());
  exitButton = new JButton("Exit");
  exitButton.addActionListener(new ExitListener());

  buttonPanel.add(addButton);
  buttonPanel.add(getWordsButton);
  buttonPanel.add(exitButton);

  return buttonPanel;
 }

 private class AddButtonListener implements ActionListener {

  @Override
  public void actionPerformed(ActionEvent e) {
   //in this method we add a word object
   //to the dictionary class
   Word w = new Word();
   w.setWord(wordText.getText());
   w.setDefinition(defText.getText());
   tech.addWord(w);

   wordText.setText("");
   defText.setText("");
  }

 }

 private class GetWordsListener implements ActionListener {

  @Override
  //here we write to the JList
  public void actionPerformed(ActionEvent e) {
   ArrayList<Word> words = tech.getWords();
   DefaultListModel model = new DefaultListModel();

   for (Word w : words) {
    model.addElement(w.toString());
   }
   wordList.setModel(model);
  }

 }

 private class ExitListener implements ActionListener {

  @Override
  public void actionPerformed(ActionEvent e) {
   System.exit(0);

  }

 }
}

Here is the Program class

package com.spconger.DictionaryProgram;

public class Program {

 public static void main(String[] args) {
  WordForm wf = new WordForm();

 }

}

Here is a picture of the program running

Code for WordForm

Here is the code for the word form. I thought I would give it to you to save a little time. I have not given you the word class or the TechnicalDictionary class. We will do them together.

package com.spconger.TechDictionary;

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.util.ArrayList;

import javax.swing.DefaultListModel;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JList;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextField;

public class WordForm {
 
 private JFrame frame;
 private JPanel borderPanel;
 private JPanel newWordPanel;
 private JPanel buttonPanel;
 private JScrollPane scrollPane;
 private JList wordList;
 private JLabel wordPrompt;
 private JTextField wordText;
 private JLabel defPrompt;
 private JTextField defText;
 private JButton addButton;
 private JButton getWordsButton;
 private JButton exitButton;
 
 private TechnicalDictionary tech;
 
 public WordForm(){
  createFrame();
  tech = new TechnicalDictionary();
 }
 
 private void createFrame(){
  frame = new JFrame();
  frame.setBounds(100, 100, 300, 300);
  frame.add(createBorderPanel());
  frame.setVisible(true);
 }
 
 private JPanel createBorderPanel(){
  borderPanel = new JPanel();
  borderPanel.setLayout(new BorderLayout());
  borderPanel.add(createNewWordPanel(), BorderLayout.NORTH);
  borderPanel.add(createScrollPane(),BorderLayout.CENTER);
  borderPanel.add(createButtonPanel(), BorderLayout.SOUTH);
  return borderPanel;
 }
 
 private JPanel createNewWordPanel(){
  newWordPanel = new JPanel();
  newWordPanel.setLayout(new GridLayout(2,2));
  wordPrompt=new JLabel("Enter Word");
  wordText = new JTextField();
  defPrompt = new JLabel("Enter Definition");
  defText = new JTextField();
  newWordPanel.add(wordPrompt);
  newWordPanel.add(wordText);
  newWordPanel.add(defPrompt);
  newWordPanel.add(defText);
  return newWordPanel;
 }
 
 private JScrollPane createScrollPane(){
  wordList = new JList();
  //add the selection listener to the list
  //wordlist.addListSelectionListener(new SelectionListener());
  scrollPane = new JScrollPane(wordList);
  scrollPane.setBounds(20, 20, 100, 200);
 
  
  return scrollPane;
 }
 
 private JPanel createButtonPanel(){
  buttonPanel = new JPanel();
  buttonPanel.setLayout(new FlowLayout());
  addButton = new JButton("Add Word");
  addButton.addActionListener(new AddButtonListener());
  getWordsButton = new JButton("Get Words");
  getWordsButton.addActionListener(new GetWordsListener());
  exitButton = new JButton("Exit");
  exitButton.addActionListener(new ExitListener());
  
  buttonPanel.add(addButton);
  buttonPanel.add(getWordsButton);
  buttonPanel.add(exitButton);
  
  return buttonPanel;
 }
 
 private class AddButtonListener implements ActionListener{

  @Override
  public void actionPerformed(ActionEvent e) {
   Word w = new Word();
   w.setWord(wordText.getText());
   w.setDefinition(defText.getText());
   tech.addWord(w);
   
   wordText.setText("");
   defText.setText("");
  }
  
 }
 
 private class GetWordsListener implements ActionListener{

  @Override
  public void actionPerformed(ActionEvent e) {
   ArrayList words = tech.getWords();
   DefaultListModel model = new DefaultListModel();
   
   for(Word w: words){
    model.addElement(w.toString());
   }
   wordList.setModel(model);
  }
  
 }
 private class ExitListener implements ActionListener{

  @Override
  public void actionPerformed(ActionEvent e) {
   System.exit(0);
   
  }
  
 }
}

Insert Update Delete

--insert update delete
use Automart

--basic insert, inserting two rows
Insert into Person (LastName, FirstName)
values('Jaunes', 'Lindsey'),
('Norton', 'Martin')

--insert with a subquery
Select * From Customer.Vehicle
Insert into Customer.Vehicle(LicenseNumber, VehicleMake, VehicleYear, PersonKey)
Values('EFG123', 'Pontiac', '1969', 
(Select Personkey From Person Where LastName='Norton' and Firstname='Martin'))

--a set of inserts, inserting a new person
--a vehicle, a registered customer
--and a vehicle service
Insert into Person(lastName, firstname)
Values('Ignatius', 'Gonzaga')

--IDENT_CURRENT ('TABLENAME') is a function that returns the last
--identity (autonumber) created in the table named
Insert into Customer.Vehicle(LicenseNumber, VehicleMake, VehicleYear, PersonKey)
Values('123TWS', 'Fiat', '2012',IDENT_CURRENT('Person'))

Insert into Customer.RegisteredCustomer(Email, CustomerPassword, PersonKey)
Values ('gonzaga@gmail.com', 'gpass', IDENT_CURRENT('Person'))

INsert into Employee.VehicleService(VehicleID, LocationID, ServiceDate, ServiceTime)
values(IDENT_CURRENT('Customer.Vehicle'), 2, getDate(), getDate())

Select * from Customer.Vehicle

Select * From Employee.VehicleService

--create a temp table
Create table PersonB
(
    personkey int,
 LastName nvarchar(255),
 FirstName nvarchar(255)
)

--insert all the records from person into personb
Select * from PersonB
Insert into PersonB(personkey, LastName, FirstName)
Select personkey, LastName, firstname from Person

--updates: these are the most dangerous statments
--make sure you have an appropriate where clause for criteria
Select * From customer.vehicle
Update PersonB
Set Firstname = 'Jason' 
Where PersonKey=1

Update Customer.Vehicle
Set VehicleMake='firebird',
LicenseNumber='GFE123'
Where VehicleID=47

--manually begin a transaction
Begin transaction

Update Person
Set LastName='Smith'
where personkey=13

Select * From Personb

--rollback undoes any thing done during the transaction
rollback tran
--commit writes it
Commit tran

Delete from Person where personKey > 20

--removes the actual table and any data in the table
Drop table PersonB
--truncate is basically the same as delete
Truncate table Person

Monday, January 26, 2015

Swing form examples

package com.spconger.FormLayouts;

import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;

public class FormLayout {
 JFrame frame;
 JPanel panel1;
 JPanel borderPanel;
 JPanel buttonPanel;
 JLabel label1;
 JLabel label2;
 JTextField textName;
 JButton button;
 JButton exitButton;
 
 public FormLayout(){
  
  createFrame();
  
 }
 
 private void createFrame(){
  frame = new JFrame();
  frame.setBounds(100, 100, 400, 200);
  frame.setDefaultCloseOperation(frame.EXIT_ON_CLOSE);
  frame.add(createBorderPanel());
  frame.setVisible(true);
 }
 
 private JPanel createBorderPanel(){
  borderPanel= new JPanel();
  borderPanel.setLayout(new BorderLayout());
  borderPanel.add(createPanel(), BorderLayout.NORTH);
  borderPanel.add(createButtonPanel(), BorderLayout.SOUTH);
  return borderPanel;
  
 }
 
 private JPanel createButtonPanel(){
  buttonPanel = new JPanel();
  buttonPanel.setLayout(new FlowLayout());
  button = new JButton("Click");
  button.addActionListener(new ButtonListener());
  exitButton = new JButton("Exit");
  exitButton.addActionListener(new ExitListener());
  buttonPanel.add(button);
  buttonPanel.add(exitButton);
  return buttonPanel;
 }
 
 private JPanel createPanel(){
  panel1=new JPanel();
  panel1.setLayout(new GridLayout(2,2));
  label1 = new JLabel("Enter Your name");
  textName=new JTextField(25);
  
  label2=new JLabel();
  panel1.add(label1);
  panel1.add(textName);
  panel1.add(label2);
  
  return panel1;
 }
 
 private class ButtonListener implements ActionListener{

  @Override
  public void actionPerformed(ActionEvent e) {
   label2.setText("Hello, " + textName.getText());
   
  }
  
 }
 
 private class ExitListener implements ActionListener{

  @Override
  public void actionPerformed(ActionEvent e) {
   System.exit(0);
   
  }
  
 }

}

Requirements and Business Rules

Requirements-Things the database has to do (for each stakeholder)

The database must • Allow registered users to post reviews
• Registered users can enter comments on Reviews
• Track books and authors
• Books reviewed will be rated
• Keep track of registered reviewers
• Assign categories to books
• The database will be searchable by Title, ISBN,
Author, Rating, Category, Date of Review, Reviewer,
publication date

Security Requirements

• Only registered users can leave reviews or comments
• A Registered user can only edit their own reviews
and comments
• All users can read and search database content

Business Rule

More how information is entered and accessed

• Only Registered users can enter reviews Comments
• Only Registered users can leave comments
• All reviews and comments must be signed
• To register a user must agree to terms,
provide a username and password and an valid email
• All passwords will be hashed
• Numerical ratings will be from 1 to 5 with 5 being best
• For a new review, if the book is not currently in the
database, the reviewer will enter it.
• Allow reviews of self-published books
• Violation of terms can get a reviewer removed from
database

Entities and Attributes

Nouns: users, Reviews, comments, books, authors, reviewers, categories, passwords, titles, ISBN, ratings, Publication Date, Email

Entities—people or objects the database is concerned with (These will probably become your tables)


Users
Books (Title, ISBN, Publication Date)
Authors (Name, Dates, Country of Origin)
Reviews (Date, Rating, Reviewer, book, Review)
Reviewers (username, password, email)
Categories (Category Name, Category Description)
Comments (Date, Book, Reviewer, Comment)

Keys

Candidate Keys –potential primary keys
Natural keys—are attributes that belong naturally to the entity
Surrogate keys—randomly assigned numbers or values
Composite keys—combinations of attributes to for a key

Sub Queries

--sub queries
use Automart
--If you want to see which service has the max price you
--need to use a subquery in the where clause
Select ServiceName, max(servicePrice) From Customer.AutoService
Group by ServiceName

Select ServiceName, ServicePrice From Customer.Autoservice
Where ServicePrice = (Select max(ServicePrice) from Customer.Autoservice)

--you can also use subqueries in the select clause
Select ServiceName, ServicePrice, (Select Max(ServicePrice) From customer.AutoService) as Maximum,(Select Max(ServicePrice) From customer.AutoService)-servicePrice as [Difference]
From Customer.AutoService


--this one goes a little crazy, the idea is that
--we will show the total count of auto's served
--the we will show the counts for each individual
--location and then what percent each represents
--of the total.
--there are three casts. The innermost cast converts
--the division to decimal to preserve the decimal part
--(count returns an integer)
--the next cast (second one in) converts the whole
--result to decimal to limit the number of decimal places
--showing. The outermost cast converst the whole expression
--to nvarchar in order to concatinate the % sign in
--
Select 
(Select Count(*) From Employee.VehicleService) Total,
LocationName, count(*) [Number per Location], 
cast(cast(cast (count(*) as decimal(4,2))
 / (Select Count(*) From Employee.VehicleService) * 100 as decimal(4,2)) 
 as Nvarchar) + '%' [Percent]
From Employee.VehicleService vs
inner join Customer.location loc
on vs.LocationID=loc.LocationID
Group by LocationName

--the in keyword returns any value that matches
--one of the values in the result set
--here the second query
Select Servicename from Customer.AutoService
Where AutoServiceID not in 
(Select AutoserviceID From Employee.VehicleServiceDetail)

--using not with in has the same result as an outer join
Select LocationName from Customer.Location
 where LocationID not in 
 (Select LocationID from Employee.VehicleService)
 
Select LocationName from Customer.Location
 where LocationID in 
 (Select LocationID from Employee.VehicleService)

 Select * From customer.AutoService

 --You can link several tables with "in"
--the logic is the same as for joins
--primary key to foreign key
--follow the relationship path to get the data
--you want
 Select LicenseNumber, VehicleMake, VehicleYear From Customer.vehicle
 Where vehicleID in
 (Select VehicleId from Employee.VehicleService 
 where VehicleServiceID in 
 (Select VehicleServiceID from Employee.VehicleServiceDetail
 Where AutoserviceID in 
 (Select AutoserviceID from Customer.AutoService 
 where ServiceName='Replace Alternator')))

 --exits returns a boolean yes/no
 Select * From Employee.VehicleServiceDetail 
 Where exists (Select AutoServiceID from Employee.VehicleServiceDetail where AutoserviceID =7)

--I often use exists to test for the existence of an object 
--if exists
 if exists
  (Select name from sys.Databases where name = 'CommunityAssist')
 Begin
 print 'yep, it''s here'
 End

 Select * from sys.Databases
 --The MagazineSubscription database is available in canvas files
 use MagazineSubscription
 Select * from Customer
 Select * From Magazine
 Select * From MagazineDetail
 Select * From SubscriptionType
 Select * From Subscription

--a correlated subquery is when the subquery uses a value in the 
 --outer query as part of its criteria
 --it results in something resembling a recursive function
 --in this case what it does is makes sure that
 --like is compared to like
 --subscription type 1 (one year) is compared only to other
 --subscription type 1's and subscription type 5 (five year) 
 --is compared only to other subscription type 5's etc.

 Select avg(SubscriptionPrice) From MagazineDetail where
 SubscriptTypeID=5

 Select SubscriptTypeId, MagDetID, SubscriptionPrice 
 From MagazineDetail md
 Where SubscriptionPrice >= 
 (Select Avg(SubscriptionPrice) From MagazineDetail md2
 where md.SubscriptTypeID=md2.SubscriptTypeID)
 And SubscriptTypeID = 5
 order by SubscriptTypeID

 

Wednesday, January 21, 2015

Joins


--Joins
--inner joins 
use Automart
Select * from Customer.RegisteredCustomer

--simple inner join using inner join syntax
--inner joins retrurn only matching records from
--the joined tables
Select LastName, Firstname, Email, CustomerPassword
from Person 
Inner Join Customer.RegisteredCustomer
on person.Personkey=Customer.RegisteredCustomer.PersonKey

--another, older way to join tables
--it uses the where clause to make the join
--it seems easier but is more dangerous and can
--result in unintended cross joins
Select LastName, Firstname, Email, CustomerPassword
From Person p, Customer.RegisteredCustomer rc
Where p.Personkey=rc.PersonKey

--intentional cross join
Select LastName, Firstname, Email, CustomerPassword
From Person p
Cross join Customer.RegisteredCustomer

--multitable inner join
 Select LastName, Firstname, Email, CustomerPassword,
 VehicleMake, VehicleYear, LicenseNumber, serviceDate
 From Person p
 inner join Customer.RegisteredCustomer rc
 on p.Personkey=rc.PersonKey
 inner join Customer.vehicle v
 on p.Personkey = v.personkey
 inner join Employee.VehicleService vs
 on v.VehicleId=vs.VehicleID

 --same join with where clause syntax
 Select LastName, Firstname, Email, CustomerPassword,
 VehicleMake, VehicleYear, LicenseNumber, serviceDate
 From Person p, Customer.RegisteredCustomer rc, Customer.Vehicle v, 
 Employee.VehicleService vs
 Where p.Personkey=rc.PersonKey
 And v.PersonKey=p.Personkey
 And v.VehicleId=vs.VehicleID
 And LastName='Anderson'

 --insert to have an unmatched value
 Insert into Customer.Autoservice( ServiceName, ServicePrice)
 values ('Replacing Upholstry',900.50)

 --outer joins return all the records from one table
 --and only matching records from the other
 --in a left join the first table named returns all its records
 --while the second one only returns matching records
 --a right outer join it is just flipped
 --the first table returns only matching records and the
 --second table returns all, matched or unmatched
 Select Distinct ServiceName, ServicePrice, vsd.AutoServiceID
 From Customer.AutoService a 
 left outer join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID

 --another outer join
 Select Distinct ServiceName, ServicePrice, vsd.AutoServiceID
 From Customer.AutoService a 
 join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID

 --see only the nonmatching records
 --the null in vsd.autoserviceID 
 --is only in the result set
 Select Distinct ServiceName, ServicePrice, vsd.AutoServiceID
 From Customer.AutoService a 
 left outer join Employee.VehicleServiceDetail vsd
 on a.AutoServiceID=vsd.AutoServiceID
 Where vsd.AutoServiceID is null

 --another
 Select LastName, firstName, rc.Personkey
 From Person p
 left outer join Customer.RegisteredCustomer rc
 on p.Personkey=rc.PersonKey
 where rc.PersonKey is null

 --and another
 Select Distinct LocationName, vs.LocationId
 From Customer.Location loc
 left outer join Employee.VehicleService vs
 on loc.LocationID=vs.LocationID
 Where vs.LocationID is null

 --a full join returns all the records
 --from both tables whether they are 
 --matched or not
 Select LocationName, vs.LocationId
 From Customer.Location loc
 full join Employee.VehicleService vs
 on loc.LocationID=vs.LocationID

Select LocationName, Month(ServiceDate) as [Month], Count(VehicleServiceID)  as [Count]
From Customer.Location loc
inner join Employee.VehicleService vs
on loc.LocationID=vs.LocationID
Where LocationName='Spokane'
Group by LocationName, Month(ServiceDate)
having count(VehicleServiceID) > 2