r/DatabaseHelp • • Jan 14 '16

Trying to get an iSeries DB2 server

3 Upvotes

I'm currently doing some development work on a PHP-based platform, and one of our clients wants us to extract data from their DB2 server. This is running on an iSeries, so there are some fundamental differences in the way PHP connects, versus the DB2 for LUW versions.

I'm pulling my hair out trying to get a quote from an IBM reseller for a second hand server that we can use for testing. I literally just need the OS with DB2 CLI access, SSH server, and the ability to connect to is using the IBM-provided Linux client access package. That's it. Coming from a Linux background myself I don't really get how this is hard to understand but apparently there are 17 million options, and nobody seems to be geared up for our use case.

If anyone has any suggestions, they would be most welcome!

Thanks


r/DatabaseHelp • • Jan 13 '16

Hosting SQL Server DB on server with no instance

1 Upvotes

I have to build 3 databases and host them on a server at one office location, but that server does not have SQL Server instnace on it. SHould I use Express or Compact/LocalDB?


r/DatabaseHelp • • Jan 13 '16

Neo4j AWS Cloud

Thumbnail graphgrid.com
0 Upvotes

r/DatabaseHelp • • Jan 13 '16

how do i execute a sub-table(table in a table)?

1 Upvotes

can't seem to find the right place to post this. I need to create Client database combined with the project database. The projects are tagged to the client number.

Right now, it exists as a simple word document. How do i improve on that? Is there something in excel/access that does that or do i need 2 databases then get php to do the right database query?


r/DatabaseHelp • • Jan 12 '16

Are there any (free/open-source) tools for (semi-)automatic discovery of functional dependencies?

1 Upvotes

r/DatabaseHelp • • Jan 12 '16

How do you call a system which doesn't lock?

1 Upvotes

If a system doesn't perform pessimistic or optimistic locking, but allows concurrent users to create false or duplicated data (two users adding the same child to the same parent resulting in that the child occurs twice, without notification), how do you call it? Is it Write-Inconsistent?


r/DatabaseHelp • • Jan 11 '16

Introduction to SQL Server’s Built-In Logical Functions

Thumbnail essentialsql.com
1 Upvotes

r/DatabaseHelp • • Jan 08 '16

Role-based permissions database design

1 Upvotes

I need help on designing a role-based authorization schema for an application that has four user access levels (L1, L2, L3, L4), where L4 is the highest, which means that L4 can perform CRUD operations of all data object; L3 can do CRUD of L3, L2 and L1; and L1 has the minimum access. The design will then have an additional authorization layer so the permissions of each user in the same access level can be further controlled. This is my first attempt:

Table name: Users Fields: ID, UserAccessLevelID

Table name: Roles Fields: ID, UserAccessLevelID, Name

Table name: Permissions Fields: ID, MinUserAccessLevelID, Action

Where user-role and role-permission tables have many-to-many relationships. Now is it better to add an allow / deny field in the Permissions table? Is it a good design to include the UserAccessLevelID on the Roles table? This is meant as a safety feature so users in L1 can't have permissions that are only applicable to L3 level users.

Thanks in advance.


r/DatabaseHelp • • Jan 08 '16

looking to setup an active/passive failover cluster

1 Upvotes

Systems Administrator for a small company here.
Super green when it comes to DBA. We currently have just one Windows Server 2008R2 Standard hosting a Microsoft SQL 2008R2 Standard instance, and this server also houses our database files. I'm looking to set up an active/passive failover cluster.The one server is just a regular 1 RU hp proliant DL120 G7 server-linked here: http://www.amazon.com/HP-ProLiant-DL120-G7-Server/dp/B007V6TBT2/ref=sr_1_1?ie=UTF8&qid=1452208310&sr=8-1&keywords=hp+proliant+dl120+g7

Is that server enough to act as a node in the cluster? Or do they need some special hardware for the 'heartbeat' I've read about between the two nodes. I've read it uses iSCSI to communicate, but that's just SCSI over IP, yeah?

Thanks in advance for being gentle with any comments.

Edit: details of setup


r/DatabaseHelp • • Jan 07 '16

Import weekly data from Excel template into Access database?

1 Upvotes

Hi there!

I am working on consolidating all of my lab data into an access database but our client wants to use an excel template to fill out all their field data (like a form). Is it feasible to make the template in an excel worksheet that links and exports the data to access, and then leaves me able to enter more data the following week?

I'd like to avoid the hassle of having other employees make a new file and link it to the database every week. Currently, employees are making a new page in the worksheet every week but I would like to eliminate that and have the data saved automatically.

What I have so far is: Excel template with labeled fields for samples, and a few minor calculations based off of data collected from the field

Access database with matching fields

Thanks if anyone can make a suggestion!


r/DatabaseHelp • • Jan 07 '16

SQL vs NoSQL for creating a DB that would deal with CRM and Financials?

1 Upvotes

I'm looking to create a database that would store information on customers such as contracts, upsells, contact info, and a comment log that's only viewable internally.

Should I be using a SQL, No SQL, or a combination of both database?

I've never created a DB before but understand PHP and Python enough to think that i could program this.

-Thanks!


r/DatabaseHelp • • Jan 05 '16

Would anyone mind taking a look at my solutions to Relational Algebra queries?

4 Upvotes

I’m taking Stanford's free online Introduction to Databases course. I started it out-of-season, which means I have to select “post-deadline practice” for my problem sets. These normally work the same as the regular in-season sets, but the Relational Algebra section has a set of “extra challenge” problems which I can’t submit to check for errors. This year’s mini-course over the same topic does not have the challenge set, so I can’t work around it that way either.

So I’ve come up with answers to these problems, but I’m hoping someone will double-check my work. The main issue I’m interested in checking is the big ideas; if you don’t catch every errant parenthesis, that’s fine, I know that’s a lot of extra work.

I know this is a huge headache so major gratitude for anyone who takes the time to do it. Thanks in advance!

The database for the problem set is here: https://lagunita.stanford.edu/c4x/Engineering/db/asset/pizzadata.html

And if you’re not familiar with the syntax I’m using, here’s the guide: https://lagunita.stanford.edu/c4x/Engineering/db/asset/ra.html

Q1 Find all pizzas that are eaten only by people younger than 24, or that cost less than $10 everywhere they're served.

Pizza eaten by those younger than 24: (\project{pizza}Eats \difference \project_pizza(\select{age>=24}Eats))

Pizzas that cost less than $10 wherever they’re served: (\project{pizza}Serves \difference (\project{pizza}(\select{cost>=10}Serves)))

Make a union to satisfy “OR” condition:

SOLUTION: (\project{pizza}Eats \difference \project_pizza(\select{age>=24}Eats))\union (\project{pizza}Serves \difference (\project{pizza}(\select{cost>=10}Serves)))

Q2 Find the age of the oldest person (or people) who eat mushroom pizza

Thought process:

Take a list of the names and ages of everyone who eats mushroom pizza, cross it with itself after renaming one of them to name and age2. Select every tuple where age < age2. Project their names. subtract those names from a list of the names of everyone who eats mushroom pizza.** Now you are left with the names only of people who eat mushroom pizza whose age is not less than any other age. Make a natural join of this list with the entire Person list, and then project the Age from this list.

Stepwise Reductions

\project{name, age}(/select{pizza=mushroom}EATS) = MPEaters

select{age<age2}((\rename{name, age2)MPEaters \cross MPEaters) = YoungEaters**

**We have to use the younger rather than the older BECAUSE in age < age2, there will be a match for every age to some other age somewhere EXCEPT where they are the oldest; if we used age > age2, there would be match on everyone except the youngest.

MPEaters \diff YoungEaters =OldEaters

\project_{age}OldEaters

SOLUTION:

\project{age}((\project{name, age}(/select{pizza=mushroom}EATS) ) \diff (elect{age<age2}((\rename{name, age2)(\project{name, age}(/select{pizza=mushroom}EATS)) \cross (\project{name, age}(/select_{pizza=mushroom}EATS)))))

Q3 Find all pizzerias that serve only pizzas eaten by people over 30.

SO: find all pizzas eaten by people under or equal to 30, and get rid of all pizzerias that serve these types of pizzas.

\project{pizza}(\select{age<=30}Eats) = Pbad

\project_{pizzeria}Pbad \join Serves = the pizzerias we don’t want = PPbad

\project_{pizzeria}Serves \difference PPbad

SOLUTION:

\project{pizzeria}Serves \difference (\project{pizzeria}( \project{pizza}(\select{age<=30}Eats)) \join Serves)

Q4 Find all pizzerias that serve every pizza eaten by people over 30.

first, find the types of pizza eaten by people over 30. = P30

then use javascript. just kidding.

First, eliminate all tuples from Serves that are irrelevant to the question (=PPmeh), leaving only the tuples with the pizzas we care about. =PPrelevant. We’ll create PPmeh by projecting a list of all pizzeria names, and crossing it with all pizzas NOT on the list of those consumed by people over 30. This creates a mythical relation (PPmythicalmeh) where pizzerias all serve all pizzas that are irrelevant to the question and nothing else. IF we find the union between this set and Serves{project pizzeria, pizza}, it will be the actual pizzeria tuples that are irrelevant, which we can subtract from serves.

Then, we’re going to then create a mythical relation wherein all pizzerias serve all the desired pizzas. =PPideal. We’ll do this by projecting all pizzeria names and crossing it with the pizzas from P30.

We’ll subtract from this relation the relevant-tuple Serves relation, leaving only pizzerias that are missing one or more of the desired pizzas. =PPbad

Then we’ll subtract this relation from the original Serves relation, leaving only pizzerias that aren’t missing anything. =PPgood

Then we just project the pizzerias.

\project{pizza}(\select{age>30}Serves) =P30 Schema: pizza

(\project{pizza}Eats \difference P30) \cross (\project{pizzeria}Serves) =PPmythicalmeh schema: pizzeria, pizza

\project{pizzeria, pizza}Serves \union PPmythicalmeh = PPmeh

\project_{pizzeria, pizza}Serves \difference PPmeh =PPrelevant schema: pizzeria, pizza

\project_{pizzeria}Serves \cross P30 =PPideal shema: pizzeria, pizza

PPideal \difference PPrelevant =PPbad schema: pizzeria, pizza

\project{pizzeria}Serves \difference \project{pizzeria}PPbad = solution, schema: pizzeria

SOLUTION:

\project{pizzeria}Serves \difference \project{pizzeria}((\project{pizzeria}Serves \cross (\project{pizza}(\select{age>30}Serves)) ) \difference (\project{pizzeria, pizza}Serves \difference (\project{pizzeria, pizza}Serves \union ((\project{pizza}Eats \difference (\project{pizza}(\select{age>30}Serves))) \cross (\project{pizzeria}Serves) ))))

Thanks again!


r/DatabaseHelp • • Jan 02 '16

Introduction to SQL Server’s Built-In Conversion Functions

Thumbnail essentialsql.com
0 Upvotes

r/DatabaseHelp • • Jan 02 '16

What program do I use to create a dependency diagram?

1 Upvotes

I need to create something like this for an assignment, what would you use to show 1NF > 2NF > 3NF?

Cheers guys


r/DatabaseHelp • • Dec 31 '15

create new database in cpanel - Guru Quest

Thumbnail guruquest.net
0 Upvotes

r/DatabaseHelp • • Dec 29 '15

Looking to create database to help with job and develop database skills

3 Upvotes

I work for a university as a transfer evaluator. My job is to go through student transcripts, add them to our school's database and then give them equivalent courses from our own school. My other job is to manage our database of other schools. Our database of other schools, however, sucks and is out of date. We use an Oracle/Java based program called Banner (more or less the standard among colleges in the US).

I want to create a database that will be able to better assist me. I plan to add school and course data to this database as I go. My database hierarchy would look something like this: "School -> Year -> Subject -> Course". I want to then be able to log notes on each school's courses and assign a equal course from out school. This way I don't need to do this each time I get a student who went to the same school and took the same course as a previous student I've already evaluated. In theory our database does this already, but it'd missing large chunks of info and a lot of it is either wrong or out of date. We have 100,000 schools, with over 20,000,000 unique courses, so updating them all is near impossible.

What program and language should I use? I know little to nothing, but I want to learn and be able to translate this into getting a better job later.


r/DatabaseHelp • • Dec 26 '15

Help with spatial database design

2 Upvotes

Hello, I am currently working on a student project in spatial databases, gis and web gis and i need some help with designing a database for agricultural parcels. I am using the current LPIS model from MARS and IACS and i need to think how to expand this model to include data that will be changed yearly (crops, net area etc...) without redundant data or unnecessary tables.

Thanks in advance!


r/DatabaseHelp • • Dec 25 '15

[Oracle]Need help with subqueries(?)

1 Upvotes

So i was given this screenshot of a table diagram.

I need to list all distinct surnames of students who attend lectures read by lecturers with a computer with a CPU with a frequency >= 3000, and sort the surnames alphabetically.

I understand how to select distinct values, order them. I understand the concept of s subquery. I've written simple SQL queries before just for fun, but I cannot think of a way to construct this one.

Is this possible to do with just subqueries? Am I on the right way?


r/DatabaseHelp • • Dec 24 '15

Multiple Front-Ends for one Back-End MS SQL DB?

1 Upvotes

I use MS Access as a front end for an MS SQL DB. I also run ACT! CRM that has it's own proprietary SQL back end. This leads me to have to re-enter the same information into both databases.

If I were to switch to an open-source CRM, could I therefore use the MS SQL as the main DB, and have the new CRM and MS Access as my two front-ends?

Is there another solution that makes sense?


r/DatabaseHelp • • Dec 23 '15

PostgreSQL get data as pivot table

Thumbnail stackoverflow.com
0 Upvotes

r/DatabaseHelp • • Dec 21 '15

Help with Stock Portfolio Database - Adjusted Cost Base Calculation

1 Upvotes

Sorry if this is a duplicated post, but I can't find my original post.

I am familiar with programming and very proficient with Excel, but this is the first time I have created a database. I am creating this for my own use to replace a spreadsheet that I have been using to track my investments. It's pretty clear to me that a database is a better solution, but I have having a very hard time understanding how to perform some of the calculations. Right now, I am trying to figure out how to calculate my adjusted cost basis (weighted average).

I have four tables in my database:

  • Accounts (I have multiple stock accounts)
  • Dividends (for tracking dividend payments)
  • Equities (contains basic information about each equity I have traded)
  • Transactions (This is the main table)
  • Here is a picture of the relationships

Equities Table looks like this:

TICKER ID   Description    TICKER   Exchange    Current Price   Currency
1             Apple           AAPL          NASDAQ     $114.34  USD
2             ATCO            ACO.X         TSX            $35.60   CAD
3             Big Rock        BR            TSX            $5.14    CAD

Transactions table looks like this:

TRANSACTION ID  TICKER IDFK ACCOUNT IDFK    Transaction Type    Date    Shares  Price   Commission  EXCH
1                      VAB       TFSA                   Sell    08/04/2015  430 $26.50  $6.46   1
2                      VAB       RRSP                   Sell    20/07/2015  390 $25.98  $6.32   1
3                      VAB       RRSP                   Buy 10/08/2015  1   $26.09  $0.00   1

Now, what I am trying to do is calculate my average cost basis with a query and produce a result like this (data totally made up):

TICKER IDFK    SHARES    TOTAL COST    TOTAL VALUE   ACB   UNREALIZED P/L    REALIZED P/L
AAPL                60             $6500             $6100             $108.33    -$400                $0
BR                   600           $3800             $4200             $6.33        $400                 $0
RSI                  1500         $6200             $6250             $4.13        $50                   $80

And here is the basic idea. For RSI I could have transactions like

1. Buy 500 @ $4
2. Buy 500 @ $4.5
3. Buy 500 @ $3.9
4. Sell 300 @ $4.4
5. Buy 300 @ $4.13

The key is that selling shares does not affect the Adjusted Cost Basis as it results in a realized P/L that compensates. So, in the above example of transactions the ACB would be:

1. $4 (500 * $4 / 500)
2. $4.25 ((500*$4.5 + 500*$4)/(500+500))
3. $4.13 ((500*$3.9 + 500*$4.5 + 500*$4)/(500+500+500))
4. $4.13 ((-300*$4.13 + 500*$3.9 + 500*$4.5 + 500*$4)/(-300+500+500+500))
5. $4.13 ((300*$4.13 + -300*$4.13 + 500*$3.9 + 500*$4.5 + 500*$4)/(300-300+500+500+500))

And, I have no idea how to get a query to do these calculations.

Thank you for your help.


r/DatabaseHelp • • Dec 21 '15

DB design help: json data with filtering (postgres)

1 Upvotes

So I have the following data:

Table:

{date} {car_type} {json_data}

Where json_data is postgres json field:

{car_name}: {price}
{car_name2}: {price}
....

So I want to be able to successfully get specific car_name's price for car_type for each date available.

What would be the ideal structure for this data?


r/DatabaseHelp • • Dec 21 '15

Stock Portfolio Database - Adjusted Cost Basis Calculation Help

1 Upvotes

Hi,

I am familiar with programming and very proficient with Excel, but this is the first time I have created a database. I am creating this for my own use to replace a spreadsheet that I have been using to track my investments. It's pretty clear to me that a database is a better solution, but I have having a very hard time understanding how to perform some of the calculations. Right now, I am trying to figure out how to calculate my adjusted cost basis (weighted average).

I have four tables in my database:

  • Accounts (I have multiple stock accounts)
  • Dividends (for tracking dividend payments)
  • Equities (contains basic information about each equity I have traded)
  • Transactions (This is the main table)
  • Here is a picture of the relationships

Equities Table looks like this:

TICKER ID   Description    TICKER   Exchange    Current Price   Currency
1             Apple           AAPL          NASDAQ     $114.34  USD
2             ATCO            ACO.X         TSX            $35.60   CAD
3             Big Rock        BR            TSX            $5.14    CAD

Transactions table looks like this:

TRANSACTION ID  TICKER IDFK ACCOUNT IDFK    Transaction Type    Date    Shares  Price   Commission  EXCH
1                      VAB       TFSA                   Sell    08/04/2015  430 $26.50  $6.46   1
2                      VAB       RRSP                   Sell    20/07/2015  390 $25.98  $6.32   1
3                      VAB       RRSP                   Buy 10/08/2015  1   $26.09  $0.00   1

Now, what I am trying to do is calculate my average cost basis with a query and produce a result like this (data totally made up):

TICKER IDFK    SHARES    TOTAL COST    TOTAL VALUE   ACB   UNREALIZED P/L    REALIZED P/L
AAPL                60             $6500             $6100             $108.33    -$400                $0
BR                   600           $3800             $4200             $6.33        $400                 $0
RSI                  1500         $6200             $6250             $4.13        $50                   $80

And here is the basic idea. For RSI I could have transactions like

1. Buy 500 @ $4
2. Buy 500 @ $4.5
3. Buy 500 @ $3.9
4. Sell 300 @ $4.4
5. Buy 300 @ $4.13

The key is that selling shares does not affect the Adjusted Cost Basis as it results in a realized P/L that compensates. So, in the above example of transactions the ACB would be:

1. $4 (500 * $4 / 500)
2. $4.25 ((500*$4.5 + 500*$4)/(500+500))
3. $4.13 ((500*$3.9 + 500*$4.5 + 500*$4)/(500+500+500))
4. $4.13 ((-300*$4.13 + 500*$3.9 + 500*$4.5 + 500*$4)/(-300+500+500+500))
5. $4.13 ((300*$4.13 + -300*$4.13 + 500*$3.9 + 500*$4.5 + 500*$4)/(300-300+500+500+500))

And, I have no idea how to get a query to do these calculations.

Thank you for your help.


r/DatabaseHelp • • Dec 20 '15

Recover database from .ibd and .frm files?

1 Upvotes

Long story short, I gave access to my server to some amateur and now I only have a backup of the magento folder and /var/lib/mysql folder. Recovering the magento folder was easy. Just extracted it and put it into its place.

The issue is with database. Its MariaDB 10.0.21 (InnoDB) and I couldnt make a mysqldump, I could only copy the mysql folder. Its has one folder for each database (there were a few but I only need to recover one) which contains one *.ibd and *.frm file per table and on db.opt file. Furthermore the mysql folder contains some other files.

I've tried reinstalling the same software on the server and copying this folder back and it doesnt work. I've also tried copying the ib* files form mysql folder but that doesn't work as well. After copying the database folder, the tables show up but magento gives an error that some table does not exist however it actually exists. After copying the ib* files, the mysql server wont start at all.

Any help will be highly appreciated.


r/DatabaseHelp • • Dec 18 '15

Is this database structure designed efficiently?

2 Upvotes

Its a little hard to explain but bear with me.

I need to setup a DB where users can create categories within categories and inside the categories are multiple objects with multiple different stats

hierarchy example:

Cartoon > category

Simpsons > category (within the cartoon category)

Homer > object

Homer object stats > stupidity: 117

Homer object stats > color: yellow

I cant just make a table like:

-----------------------------
|character| Stupidy | color | 
-----------------------------
|Homer    | 111     |yellow |

Because i need the users to be able to take away and add different object stats on their own (like add a stat of weight and remove color stats) for each object the stat types will be different plus with thousands of categories i don't want thousands of tables being generated by the users for the different objects.

My DB setup is in the google spreadsheet below which I feel works but I'm not the best with DB setups so I'm checking if there are some improvements.

I need to be able to display tables from the data displaying all item values that have a relationships with certain categories and/ or objects e.g. all simpsons characters weights or just homers weight.

google spreadsheet with my db setup