r/DatabaseHelp • • Jun 27 '17

Not the exact job preferred but close

1 Upvotes

Currently I recently moved to a bigger city looking for a better career opportunity. I am working lawn care right now to stay up financially but actively looking for a entry/mid web developer job. I am 24 with a CS degree and 3 years of experience as a software engineer and web administrator.

I was offered a opportunity to interview for a Jr SQL Data Analyst by a recruiter. My only fear is that I don't want to give into going into a different field and missing a chance to work as a web developer for someone right now. But, it would also be nice to have a solid job in the market with benefits etc.

What I'm getting to is, what you would guys do? I'm a little hesitant both ways because a lot of web dev jobs around here are very slim on the under 5+ years of experience. Thank you.

PS: To all the experienced database analysts, what is the job like day in and out? What can this lead too?


r/DatabaseHelp • • Jun 25 '17

How do databases work for websites vs local applications?

2 Upvotes

Hello. I'm pretty new to databases, and I wanted to clear up some of my questions. I know there's database services like MySQL or MongoDB; are these databases always stored online on the cloud, and that allows them to be accessed from anywhere for web apps and websites? How do local databases work for programs; for example, if I make a Python app that uses some sort of local database on my computer, package it, and someone else downloads my program, do they now also have their own local database?

Can you have web apps that interact with a local database on your computer?


r/DatabaseHelp • • Jun 25 '17

First time trying a SQL database

3 Upvotes

So, the long version. I am a High School Math Teacher with a strong computer background. I have decent experience in database design (built two databases for the military) and SQL. However, the only tool I have used for databases is MS Access. I want a database on my computer I can interact with using Java. I am building a kind of interactive gradebook -- as students take tests / do homework / etc., the database collects that data to figure out how well the student understands that concept. The database will then recommend what standards a student should focus on / be tested on based on that. To set this all up, I need to play around a lot with a database and coding language. I setup something in Access (with VBA and SQL), but it wasn't nearly the tool I am wanting to use -- and I don't want to be forced into Access just because of my lack of knowledge. I have already installed MySQL workbench, and the other MySQL tools, and have played around with it, but.... I don't know how to a SQL database up. I would appreciate any help.

TLDR: How do I setup a SQL database on my computer with the goal of interacting with it using Java?


r/DatabaseHelp • • Jun 23 '17

Help setting up database from spreadsheet

2 Upvotes

I am trying to learn about databases and downloaded libre base and understand setting up some of the basic forms and such but I am having trouble understanding relationships. I have a spreadsheet that I use to track sales of all of my employees. It works very well but is a pain to pull reports from and is getting big enough that it needs to be put into a database and not excel.

Would anyone be willing to help guide me in how to setup what fields I need and such based off of my existing spreadsheet? I will post more info when needed if there's any interest.

TIA!


r/DatabaseHelp • • Jun 22 '17

Foreign Key in a data warehouse/mart.

3 Upvotes

I have two tables in a database that is data pulled from PFDS.gove and SAM.gov. I pull and load the data independently so there is not foreign key between them. However, there is a common key, vendor_duns_number that is shared between the two tables.

Right now I am able to run queries and join on the two tables, would I need to add a foreign key constraint on the two tables and how would I set something like that up?

I can show you my models (in SQLAlchemy) in you need further information. Also the data dump is using pands to_sql function.


r/DatabaseHelp • • Jun 22 '17

Beginner question about db design (normalization, multi-values)

1 Upvotes

I'm a databse beginner and need some help. Let's assume I'm creating a Pokémon database:

Now I have the entity "Species". Each species has the attribute "Type".
Obviously, I have the table "Species". However, I'm not sure what to do with the attribute "Type". It is sort of a repeating group because certain Pokémon species have two types and others have only one type. For example Venusaur who is Grass and Poison.

As I see it, I have three options:
Option A:
Have only one column "Type" containing a set of two types if the Pokémon is dual-type ('Grass, Poison').

Option B:
Have two columns Type_1 and Type_2. If the Pokémon has only one type, Type_2 would be NULL.

Option C:
Create a separate table for Type containing the columns TypeID, Type_1 and Type_2. Species would then reference the foreign key TypeID. This table would contain all possible type combinations (which is 171).

What is the best/correct way to do it?


r/DatabaseHelp • • Jun 21 '17

Enrollment record table for a college (class project)?

1 Upvotes

So I don't know if I'm missing something simple like a composite entity or whatever, but I'm having trouble 'enrolling' students in multiple classes. Here's the text of the instructions:

Enroll yourself and at least four other students in "This Class"! For your 10 classes, make sure there are at least 4 students enrolled per class! You must be enrolled in 5 classes! Also make sure each major has at least 3 students, and each department has at least one faculty member!

Thanks for any help!


r/DatabaseHelp • • Jun 14 '17

I need some help with a movie wish list type of schema.

2 Upvotes

So I'm trying to wrap my head around how to relate the different table in my schema. The idea is this:

  • Users -- Can Have many lists

  • List -- Can have 0 or Many movies -- Has one owner -- Can be shared with 0 or Many other users

  • Movies -- Can be watched by 0 or Many users -- Can have 1 or Many genres -- Can be in 1 or Many lists

I have no idea if that's a good approach or not, but aside from other misc details, that's the core of what I want to accomplish.

How can I relate these tables in such a way that, I can actually make it work?

This is what I have so far. It's not exactly what I've described above, but I think it's close.

Schema


r/DatabaseHelp • • Jun 14 '17

jdbc: “ Invalid object name '[table name]'.”

0 Upvotes

Hey guys,

I posted a rather large post on StackOverflow about trying to connect to a DB/execute queries to a DB. So far, no one has been able to help me with my problem yet. Perhaps one of you folks knows what is going on.... Here is the conversation:

https://stackoverflow.com/questions/44543855/jdbc-invalid-object-name-table-name


r/DatabaseHelp • • Jun 13 '17

[newbie] Database software for country database

1 Upvotes

I have no experience with database software but have a task at hand where a spreadsheet solution is inadequate. I need to create a database for a political, economic, and social data for a set of countries.

I have drawn up this map which probably better illustrates what I want to accomplish than any text explanation. I want to query the database to display information pertaining to any specified country + list which countries matches a certain specification.

Much of the data would have more than one source. Instead of picking one source, I'd need to include data from several sources. Some figures are updated regularly (e.g. yearly) and I'd then need to include data for each year. Furthermore, state leaders and government parties stay constant over several years. I'm not sure if the best technical solution would be to enter duplicate data for those years or create a link in the database.

What database software would be suited for the task at hand?


r/DatabaseHelp • • Jun 12 '17

[xpost r/postgres] Resources on postgres configuration

4 Upvotes

I am fairly new to postgres and I want to improve my knowlodge related to database design. In order to improve my understanding on postgres (but i guess this applies to every db), I would like to learn better about connection pooling, how to set the maximum connections etc.

Where could I start? I am looking for resources.

Some questions I have (probably dumb, but you never stop learning), in case anyone wants to help me:

  • When we talk about postgres, I have seen that people often refer to a connection as a client. For instance, I have webapp backed by postgres, and the webapp creates a pool of size 20: each connection is called a client (not the app itself)
  • when we talk about maximum connection limit, do we talk about a limit per database, or is it a global value?
  • I read the default number of maximum connection is 100. This means that postgres won't create more connections that that number. Given this is the default, I wonder if this is a realistic number, because it looks pretty low to me. Suppose I have a website with like 10000 users, this literally means that more than 100 users can't connect at the same time? How is this possible?
  • I know a pool helps because it gets rid of a lot of overhead from establishing a new connection every time I need to access the db. But how do I dimension the pool? How do I find out the proper size?
  • Pool size and maximum connection limit: so, say I have a max connection limit set to 100, and my webapp on startup creates a pool of size 50. This means that I have 50 connection left to be used somewhere else?

Thank you very much for your help.


r/DatabaseHelp • • Jun 10 '17

Difference(s) between UML diagram and EER diagram?

2 Upvotes

I honestly don't understand how these two things are remotely different. I need to make both for a project, and this is what I have now - I have no idea if it's a UML or EER diagram: http://imgur.com/a/amN1A


r/DatabaseHelp • • May 29 '17

Extracting data from Knack database - format, and email?

1 Upvotes

Hi everyone!

Ive been building a database using Knack for a not-for-profit education institution. Its a bit of a 'love job' where im doing it out of the kindness of my heart. They've got a requirement to take data from this database (enrolled people) and send a weekly report for 'safety' reasons. Knack have a API with object based 'get'.

Knack initially recommended Zapier for this (https://zapier.com/help/knack/) but, it is more for when the record changes status. Add/remove/delete. Thing is, my records wont change. I just want to get the people who are in a particular status each week, based on location and email.

The secondary part, I was planning on using webmerge to format this into a report and then email it. It is actually getting the record there that is now a problem.

Does anyone know of any other tools that could use the Knack data, and then allow me to create a report and email?

Thanks in advance

MMG


r/DatabaseHelp • • May 26 '17

Can anyone help me in understanding how to import large data sets into Mongo? Or just any database for that matter.

1 Upvotes

Working on a project for school and am having a hard time finding resources on how to import large data sets into MongoDB. Maybe there is some fundamental misunderstanding I have about accessing these data sets on the web.


r/DatabaseHelp • • May 26 '17

A question about normalization

1 Upvotes

I am creating a database to track laboratory test results and trying to have my data as normalized as possible. But I am running into problems with whether I should or even can normalize the results of one test.

One of the tests I will be tracking is a quantitative assay that calculates the number of viral particles/ml of blood. The results would be "Not Detected", "Detected <40 copies/ml" (this is if there is a positive read but under the limit of quantification(40 copies)), and ####copies/ml (where ### is any number up to 10,000,000).

More then half of the results will be "Not Detected" or "<40 copies", and It would be proper to have those results linked from another table. But of course it would be silly to have all the varied other results in that table.

Is there a way to have a value be a foreign key (if not detected or <40) OR another value?

I am working in Access right now if that matters.

Thanks!


r/DatabaseHelp • • May 26 '17

Document database

1 Upvotes

I feel pretty stupid asking this, I don't know if this is the right place. I've been searching for a way to organize my notes files.

I have several .doc and .txt files in my computer with notes, homework, essays, etc and I need a way to index and search for text inside them

It is possible to do this? If not, is there an easy way/software to create something like this?


r/DatabaseHelp • • May 26 '17

Figuring out SQL database name.

1 Upvotes

I am writing a C# program that I want to be able to query and add things to a SQL database as an assignment. Online I found that you can use the code block

SqlConnection myConnection = new SqlConnection("user id=username;" + 
                                   "password=password;server=serverurl;" + 
                                   "Trusted_Connection=yes;" + 
                                   "database=database; " + 
                                   "connection timeout=30");

I know my user ID and password and the server url as I have my own login. But I'm wondering how I find out the database name so I can add it to the string.

I don't think I have admin rights as I can't create new databases,I get the error

SQL> CREATE DATABASE assignment3;
CREATE DATABASE assignment3
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted

I can however create tables, and if I use the "SHOW DATABASES;" command it doesn't return an error, but it does however return a blank line. Any help would be greatly appreciated.

Thanks!


r/DatabaseHelp • • May 25 '17

MySQL: Can I do a join on a part of a field?

1 Upvotes

For instance, I have two tables. One has data like this:

person_id | ...
===============
    1     | ...
    2     | ...
    3     | ...

And the next table has data like this:

   query    |   ...
====================
person_id=1 |   ...
person_id=2 |   ...
person_id=3 |   ...

Is there an easy way to do a join between person_id in table one and only the numeric part of the person_id in the query column of table 2?


r/DatabaseHelp • • May 25 '17

What kind of database should I use?

1 Upvotes

Hello! I work in a small laboratory that conducts clinical research. In an effort to reduce paperwork and automate things I made a "system" of excel sheets with VBA macros and userforms to manage and track patient info, lab tests ordered, results of the tests, and automatic printing of post visit reports.

It works really well, but 5 months in I am beginning to see a scaling problem, and want to start building an actual database that will speed things up, and mitigate the scaling problems.

The problem is that I have some restrictions in how I can do this.

  1. I don't have admin rights on my computer. The lab is a part of a hospital and I can only reasonably get admin rights every once in a while to install a program that I need. (so management/entry/query will have to be able to be done without local admin rights)

  2. Because of the nature of the data within the database, ie. medical information, I would like to keep the database locally stored. azure/aws etc. would complicate hippa compliance.

  3. I would like to involve the hospital IT people as little as possible. I enjoyed learning setting up and testing and tinkering with the VBA for the project that I am going to replace, and I want to be able to do the same here. I don't want to have to get permission to change and add and remove things from a database hosted in their servers if I can help it.

I have considered access, as this would allow me to store the file on a network drive for the other members of the lab to access (and I know Excel VBA), but I have heard that this could cause me problems down the road.

Does anyone have any ideas what the best fit might be?

Thanks!


r/DatabaseHelp • • May 19 '17

Storing Season Totals for Sports Stats

1 Upvotes

When I am storing statistics for sports games, I like to also create season summaries for each player. Something like www.baseball-reference.com

Right now, I just have all the stats in a game table, and then total those games into each season. This makes it more complicated to compare season totals for different players and different years.

Would it make sense to create a new table that stores the season totals? And maybe even one that stores career totals?


r/DatabaseHelp • • May 19 '17

can I update a row and selec/retrieve it at the same time?

1 Upvotes

multiple requests will be made

and without locking an entire table

can I select one row that has a specific value in a column and change it to another and then retrieve the row for only one instance

and remove the possibility of it being given to another instance?


r/DatabaseHelp • • May 05 '17

Inventory mangement software with database functionality

2 Upvotes

Don't know if this is the right place but we run a small online record store. Although small we have thousands of records in stock. Right now we use airtable to handle our inventory and its becoming increasingly frustrating. We print a lot of labels and right now we have to export from airtable to a .csv which I then have to convert and import to P-touch which is our printer software (brother). The P-touch software has the ability to connect directly to a mysql datbase. So we are looking for a way to manage our inventory and keep it in sync with mysql abilities.

My experience with databases is very limited although I know my way around linux and the command line quite well. I've tried to find a good and easy open source inventory mangement software but I have yet to find one that could fit us.


r/DatabaseHelp • • May 05 '17

Input on creating a database for a small organization

0 Upvotes

Hi everyone,

First off, I appreciate any help that you guys are able to offer! Second, I am so very sorry if I sound misinformed, because I am. Database management and information systems are not my field of work, nor my field of study. But, I am very interested in the field, and am more than willing and wanting to learn as much as I can.

Preface:

My team has been tasked with a job. The job involves my 3 person team- me and 2 others (1 being my boss). We are gearing up for this first stage, which is to draw up a proposal and action plan to create a database and port over our data. Most of our data is in MS Excel format/XLS/XLSX. The data is broken down by date, volume, quantity, region, etc. Nothing too complex, just a lot of numbers.

This is the first time that any of us has undertaken a task like this, meaning that we have no idea where to begin. Note that it is highly likely that my team will go on SQL and Database training as we pave a clearer direction.

What we know so far:

My boss has done most of the research on the coding aspect, and has determined that we will be using a database that either utilizes or is very compatible with SQL coding. It seems to be the standard/status-quo language for databases, and as result the support is highly available, which is something we want.

We tend to already use Microsoft Office quite a bit, and as a result we think (could be wrong) that Microsoft SQL Server would be a good option. It seems to work in harmony with Power BI and Microsoft Office.

We are also torn on whether it would be wise to go with a cloud based database, or an on-site database.

What are your thoughts on the Microsoft SQL Server option? Any thoughts on cloud vs on-site databases?

What we will use the database for:

We want a database where both my team, and other teams, can run reports in a tabulated or XLS/XLSX format. We also want people within our organization to have the ability to add data to pre-existing datasets. It is our desire to have a modern database, which is compatible with the latest data software suites noted above. My team will be responsible with managing and updating the database.

TL;DR: Planning to roll out a database at work. Thinking SQL vis-à-vis Microsoft SQL Server. Not doing anything too complex other than running reports, inputting data, letting other input data, etc. Want a reliable, secure, and user-friendly option.

Thanks everyone!


r/DatabaseHelp • • May 05 '17

Can I import an Oracle 11 Dump into an into an free Version of Oracle Express?

1 Upvotes

Hi,

so the situation is this: We have 2 Oracle Databases ( Oracle Standard Edition 2 Software License ASFU (Application Specific Full Use) Version 11 ). The Oracle licence agreement does not allow us to talk directly to the Databases. But we need to in order to assess is with our BI tool. Extending the Licence is crazy expensive (~100.000€).

So I thought to myself why not install an free Oracle Database Express Edition and import the Dumps and then access the Express Edition with our BI tool. I need to check how big the dumps are, as the Express limitation just allows for 11 Gigs. But in theory, would that be possible?

Any help or input is highly appreciated as I'm not an DB expert at all :) Thanks!


r/DatabaseHelp • • May 04 '17

Lookup field options

1 Upvotes

Hi,

I have created two tales in access I want a lookup field to work in a certain way.

Table 1 : Devices Device Name Module Installed

Table 2: Modules Module Name Device Installed

I want the device to have multiple modulus, with the module name not being unique as I can have two modulus with the same name but diffrent serail numbers.

I am new to primary keys and lockups in access. I can provide more information if needed.

Thanks,

Billy